TSQL Pivot without aggregate function

Question

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

User · Answer

The OP didn t actually need to pivot without agregation but for those of you coming here to know how see   sql parameterised cte query  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  amp  have not run the SQL  I hope  you get the idea

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] TSQL Pivot without aggregate function

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to pivot

Examples related to pivot-without-aggregate