Adrian Cross
asked on
SQL - Update pivot table
Hi, I have two tables, the first one is like the one below:
-------------------- ------------------
ColumnName Value
--------------------- -----------------
C1 20
C7 35
C12 5
The second table has 12 columns. What I would like is to get this result from the previous table.
--------------------------
C1 C2 C3 C4 C5 C6 C7 C8 C9 C10 C11 C12
--------------------------
20 35 5
The entries on the first table (ColumnName) can vary and can refer to any column on the second table. Is there a way to do this??
Thanks!
-------------------- ------------------
ColumnName Value
--------------------- -----------------
C1 20
C7 35
C12 5
The second table has 12 columns. What I would like is to get this result from the previous table.
--------------------------
C1 C2 C3 C4 C5 C6 C7 C8 C9 C10 C11 C12
--------------------------
20 35 5
The entries on the first table (ColumnName) can vary and can refer to any column on the second table. Is there a way to do this??
Thanks!
Chris Luttrell
Are the 12 column names consistent? This will have to be hard coded if they are consistent or we will have to use dynamic sql if we have to query for the names. Also, what version of SQL are you on, want to make sure we use all the best tools available?
ASKER
Hi, yes the 12 column names will be the same.
Im using SQL 2012
Im using SQL 2012
You didn't specify a data type for "value", so I'll assume it's some type of numeric (int / decimal / etc.).
SELECT
MAX(CASE WHEN ColumnName = 'C1' THEN Value END) AS C1,
MAX(CASE WHEN ColumnName = 'C2' THEN Value END) AS C2,
MAX(CASE WHEN ColumnName = 'C3' THEN Value END) AS C3,
MAX(CASE WHEN ColumnName = 'C4' THEN Value END) AS C4,
--...
MAX(CASE WHEN ColumnName = 'C12' THEN Value END) AS C12
FROM dbo.table1
SELECT
MAX(CASE WHEN ColumnName = 'C1' THEN Value END) AS C1,
MAX(CASE WHEN ColumnName = 'C2' THEN Value END) AS C2,
MAX(CASE WHEN ColumnName = 'C3' THEN Value END) AS C3,
MAX(CASE WHEN ColumnName = 'C4' THEN Value END) AS C4,
--...
MAX(CASE WHEN ColumnName = 'C12' THEN Value END) AS C12
FROM dbo.table1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.