TSQL Pivot without aggregate function - Printable Version +- 0Day Forums (https://zeroday.vip) +-- Forum: Coding (https://zeroday.vip/Forum-Coding) +--- Forum: Database (https://zeroday.vip/Forum-Database) +---- Forum: Microsoft SQL Server (https://zeroday.vip/Forum-Microsoft-SQL-Server) +---- Thread: TSQL Pivot without aggregate function (/Thread-TSQL-Pivot-without-aggregate-function) Pages:
1
2
|
TSQL Pivot without aggregate function - theiss53 - 07-31-2023 I have a table like this... CustomerID |DBColumnName |Data -------------|---------------|---------- 1 |FirstName |Joe 1 |MiddleName |S 1 |LastName |Smith 1 |Date |12/12/2009 2 |FirstName |Sam 2 |MiddleName |S 2 |LastName |Freddrick 2 |Date |1/12/2009 3 |FirstName |Jaime 3 |MiddleName |S 3 |LastName |Carol 3 |Date |12/1/2009 And I want this... Is this possible using PIVOT? CustomerID |FirstName |MiddleName |LastName |Date ------------|------------|--------------------|----------------|------- 1 |Joe | S |Smith |12/12/2009 2 |Sam | S |Freddrick |1/12/2009 3 |Jaime | S |Carol |12/1/2009 RE: TSQL Pivot without aggregate function - circuiteer914626 - 07-31-2023 You can use the MAX aggregate, it would still work. MAX of one value = that value.. In this case, you could also self join 5 times on customerid, filter by dbColumnName per table reference. It may work out better. RE: TSQL Pivot without aggregate function - karrahk - 07-31-2023 SELECT main.CustomerID, f.Data AS FirstName, m.Data AS MiddleName, l.Data AS LastName, d.Data AS Date FROM table main INNER JOIN table f on f.CustomerID = main.CustomerID INNER JOIN table m on m.CustomerID = main.CustomerID INNER JOIN table l on l.CustomerID = main.CustomerID INNER JOIN table d on d.CustomerID = main.CustomerID WHERE f.DBColumnName = 'FirstName' AND m.DBColumnName = 'MiddleName' AND l.DBColumnName = 'LastName' AND d.DBColumnName = 'Date' Edit: I have written this without an editor & have not run the SQL. I hope, you get the idea. RE: TSQL Pivot without aggregate function - brava435047 - 07-31-2023 Ok, sorry for the poor question. gbn got me on the right track. This is what I was looking for in an answer. SELECT [FirstName], [MiddleName], [LastName], [Date] FROM #temp PIVOT ( MIN([Data]) FOR [DBColumnName] IN ([FirstName], [MiddleName], [LastName], [Date]) )AS p Then I had to use a while statement and build the above statement as a varchar and use dynmaic sql. Using something like this SET @fullsql = @fullsql + 'SELECT ' + REPLACE(REPLACE(@fulltext,'(',''),')','') SET @fullsql = @fullsql + 'FROM #temp ' SET @fullsql = @fullsql + 'PIVOT' SET @fullsql = @fullsql + '(' SET @fullsql = @fullsql + ' MIN([Data])' SET @fullsql = @fullsql + ' FOR [DBColumnName] IN '+@fulltext SET @fullsql = @fullsql + ')' SET @fullsql = @fullsql + 'AS p' EXEC (@fullsql) Having a to build @fulltext using a while loop and select the distinct column names out of the table. Thanks for the answers. RE: TSQL Pivot without aggregate function - yesman283 - 07-31-2023 yes, but why !!?? Select CustomerID, Min(Case DBColumnName When 'FirstName' Then Data End) FirstName, Min(Case DBColumnName When 'MiddleName' Then Data End) MiddleName, Min(Case DBColumnName When 'LastName' Then Data End) LastName, Min(Case DBColumnName When 'Date' Then Data End) Date From table Group By CustomerId RE: TSQL Pivot without aggregate function - maloriefntcn - 07-31-2023 The OP didn't actually need to pivot without agregation but for those of you coming here to know how see: [To see links please register here] The answer to that question involves a situation where pivot without aggregation is needed so an example of doing it is part of the solution. RE: TSQL Pivot without aggregate function - teeganrpjhvyh - 07-31-2023 WITH pivot_data AS ( SELECT customerid, -- Grouping Column dbcolumnname, -- Spreading Column data -- Aggregate Column FROM pivot2 ) SELECT customerid, [firstname], [middlename], [lastname] FROM pivot_data PIVOT (max(data) FOR dbcolumnname IN ([firstname],[middlename],[lastname])) AS p; RE: TSQL Pivot without aggregate function - kation617316 - 07-31-2023 Try this: SELECT CUSTOMER_ID, MAX(FIRSTNAME) AS FIRSTNAME, MAX(LASTNAME) AS LASTNAME ... FROM ( SELECT CUSTOMER_ID, CASE WHEN DBCOLUMNNAME='FirstName' then DATA ELSE NULL END AS FIRSTNAME, CASE WHEN DBCOLUMNNAME='LastName' then DATA ELSE NULL END AS LASTNAME, ... and so on ... GROUP BY CUSTOMER_ID ) TEMP GROUP BY CUSTOMER_ID RE: TSQL Pivot without aggregate function - k527 - 07-31-2023 This should work: select * from (select [CustomerID] ,[Demographic] ,[Data] from [dbo].[pivot] ) as Ter pivot (max(Data) for Demographic in (FirstName, MiddleName, LastName, [Date]))as bro RE: TSQL Pivot without aggregate function - hydroxides454915 - 07-31-2023 Here is a great way to build dynamic fields for a pivot query: --summarize values to a tmp table declare @STR varchar(1000) SELECT @STr = COALESCE(@STr +', ', '') + QUOTENAME(DateRange) from (select distinct DateRange, ID from ##pivot)d order by ID ---see the fields generated print @STr exec(' .... pivot code ... pivot (avg(SalesAmt) for DateRange IN (' + @Str +')) AS P order by Decile') |