Post by Manoj KumarIs it possible to convert columns into rows.
Converting this
Date Computer Fridge
21-Jun-13 2 3
20-Jun-13 3 4
19-Jun-13 7 9
into
Items 21-Jun-13 20-Jun-13 19-Jun-13
Computer 2 3 7
Fridge 3 4 9
SELECT MAX(CASE WHEN tbl.Date = '20130621'
THEN CASE WHEN n.n = 1 THEN Computer
WHEN n.n = 2 THEN Fridge
END
END) AS [21-Jun-13],
MAX(CASE WHEN tbl.Date = '20130620'
THEN CASE WHEN n.n = 1 THEN Computer
WHEN n.n = 2 THEN Fridge
END
END) AS [20-Jun-13],
MAX(CASE WHEN tbl.Date = '20130619'
THEN CASE WHEN n.n = 1 THEN Computer
WHEN n.n = 2 THEN Fridge
END
END) AS [19-Jun-13]
FROM tbl
CROSS JOIN (SELECT n = 1 UNION ALL SELECT 2) AS n
This is the pattern of a combined pivot and unpivot (without using
the PIVOT and UNPIVOT keywords in SQL Server that I recommend staying
away from.)
As you may note the code is repetitive and the column names are fixed.
This is because a SELECT statement returns a table, and a table is
supposed to mode a unique entity with distinct attributes. Thus dynamic
naming does not fit into this pattern.
If you always want three days, you can insert the data into a temp
table, and then use sp_rename to rename the columns. If you have a variable
number of days, you will need to first read the available dates and
then compose dynamic SQL. There is all reason to investigate if you
can do this more easily client-side.
--
Erland Sommarskog, Stockholm, ***@sommarskog.se