Help with Pivot / Unpivot

Question

I have a table as below

Name    Priority    Date    
-------------------------
A         2          d1
B         3          d2

How to write a query to achieve the below output

ColumnNames   d1      d2
--------------------------
 Name         A       B
 Priority     2       3

Thanks

Answer 1

Here is the solution I promised:

EDIT: I modified my answer to answer additional questions by the OP.

A few things to note:

• The STUFF function: This is used to convert the XML string into a regular string (and to remove the first comma)
• Group By (I stole this from OMG Ponies): You need to do this to ensure you don't have any duplicate dates
• Make sure there aren't too many dates in the table before you run this. Too many columns can be a problem
• NVARCHAR: I used this instead of VARCHAR for the @sql variable because sp_ExecuteSQL requires it.
• CONVERT(VARCHAR, DateColumn, 101): I did this because unless you convert the date to a string, this wont work. 101 results in this: mm/dd/yyyy but you can use whatever you need (make sure the 2 times it is used in this script matches)
• In order for this to work for multiple columns, you must first use UNPIVOT and convert all columns to the same datatype (more information below the code)
• It is important to note that in order to concatenate strings, they must be of the same datatype, with the same size (in my case, they are both NVARCHAR(MAX))

Read this page for more information on converting dates to strings.

With that said, here is the code:

-- table with multiple columns
CREATE TABLE #TBL ( 
    NameColumn VARCHAR(10), 
    PriorityColumn INT,
    AnotherColumn FLOAT,
    DateColumn DATETIME 
)


-- Insert the test data
INSERT INTO #TBL VALUES ('a', 1, 7.2, '1/1/2000')
INSERT INTO #TBL VALUES ('a', 2, 8.9, '1/2/2000')
INSERT INTO #TBL VALUES ('a', 2, 53.2, '1/3/2000')
INSERT INTO #TBL VALUES ('a', 3, 9.12, '1/4/2000')
INSERT INTO #TBL VALUES ('b', 2, 1.26, '1/1/2001')


DECLARE
    @sql NVARCHAR(max),
    @dates NVARCHAR(max)


-- I separated this to make the code easier to read
SET @dates = STUFF(
    (
        SELECT N',[' + CONVERT(VARCHAR, DateColumn, 101) + ']' AS [text()]
        FROM #TBL
        GROUP BY DateColumn
        ORDER BY DateColumn
        FOR XML PATH('')
    ), 1, 1, N''
)


-- I will break this part of the code up below
SET @sql = N'SELECT
    *
FROM (
    SELECT
        ColumnName,
        ColumnValue,
        CONVERT(VARCHAR, DateColumn, 101) AS DateString
    FROM (
        SELECT
            CAST(NameColumn AS VARCHAR(100)) AS NameColumn,
            CAST(PriorityColumn AS VARCHAR(100)) AS PriorityColumn,
            CAST(AnotherColumn AS VARCHAR(100)) AS AnotherColumn,
            DateColumn
        FROM #TBL
    ) P
    UNPIVOT (
        ColumnValue
        FOR ColumnName IN (NameColumn, PriorityColumn, AnotherColumn)
    ) UNPIV
) P2
PIVOT (
    MAX(ColumnValue)
    FOR DateString IN (' + @dates + N')
) PIV'

EXECUTE dbo.sp_ExecuteSQL @sql


DROP TABLE #TBL

Lets run through this a little

-- I first do an UNPIVOT on all of the columns I want to pivot on, at the same time, converting them to the same datatype
SELECT
    ColumnName,
    ColumnValue,
    CONVERT(VARCHAR, DateColumn, 101) AS DateString
FROM (
    SELECT
        CAST(NameColumn AS VARCHAR(100)) AS NameColumn,
        CAST(PriorityColumn AS VARCHAR(100)) AS PriorityColumn,
        CAST(AnotherColumn AS VARCHAR(100)) AS AnotherColumn,
        DateColumn
    FROM #TBL
) P
UNPIVOT (
    ColumnValue
    FOR ColumnName IN (NameColumn, PriorityColumn, AnotherColumn)
) UNPIV

Once I do this, the data will look something like this:

ColumnName  ColumnValue DateString
----------------------------------
NameColumn      a       01/01/2000
PriorityColumn  1       01/01/2000
AnotherColumn   7.2     01/01/2000
NameColumn      a       01/02/2000
PriorityColumn  2       01/02/2000
AnotherColumn   8.9     01/02/2000
NameColumn      a       01/03/2000
PriorityColumn  2       01/03/2000
AnotherColumn   53.2     01/03/2000
NameColumn      a       01/04/2000
PriorityColumn  3       01/04/2000
AnotherColumn   9.12     01/04/2000
NameColumn      b       01/01/2001
PriorityColumn  2       01/01/2001
AnotherColumn   1.26     01/01/2001

Then we can use PIVOT like this to get all the columns we need:

PIVOT (
    MAX(ColumnValue)
    FOR DateString IN (' + @dates + N')
) PIV

Answer 2

Is this what you're looking for:

create table NameAndDate (NameCol varchar(200), DateCol datetime);

insert into NameAndDate (NameCol, DateCol) values ('A', '2010-03-04');
insert into NameAndDate (NameCol, DateCol) values ('B', '2010-03-05');

select * from NameAndDate;

select * from NameAndDate
pivot (
max(NameCol) 
for DateCol 
in ([2010-03-04], [2010-03-05])) 
as PivotResults;

This gives me the following results:

   NameCol     DateCol
---------------------------------------
1  A           2010-03-04 00:00:00.0000
2  B           2010-03-05 00:00:00.0000


   2010-03-04  2010-03-05
-------------------------
1  A           B

Note this requires you to know the dates ahead of time, when you write the query (unless you use dynamic SQL as mentioned by Gabriel).

Edit:

I tried using OMG Ponies' approach, but had to modify it like this:

declare @Dates nvarchar(max)
set @Dates = 
    (select '['+ convert(varchar, NameAndDate.DateCol) + '],' 
    from NameAndDate
    group by NameAndDate.DateCol
    order by NameAndDate.DateCol
    for xml path(''))
set @Dates = left(@Dates, len(@Dates) - 1)

declare @SQL nvarchar(4000)
set @SQL = 
    'select *  
    from NameAndDate
    pivot (
    max(NameCol) 
    for DateCol in (' + @Dates + ')) 
    as PivotResults'

exec sp_executesql @SQL

This gives me these results:

   Mar 4 2010 12:00AM   Mar 5 2010 12:00AM
------------------------------------------
1  A                    B

Answer 3

Use:

DECLARE @SQL NVARCHAR(4000)
DECLARE @dates NVARCHAR(max)

SET @dates = SELECT '['+ t.date +"],"
               FROM TABLE t
           GROUP BY t.date
           ORDER BY t.date
            FOR XML PATH('')

@SQL = 'SELECT * 
          FROM TABLE 
         PIVOT(MAX(name) FOR date IN (@dates)) AS pvt'

BEGIN 

  EXEC sp_executesql @SQL, N'@dates NVARCHAR(max)', @dates

END