Convert Rows Into Columns in new Table

Question

I have a table Table which have some columns and I want to convert column values as columns for another table.

I am attaching one screen shot of my requirement.

The upper table is my main table say Table1 and the below is my required table. Thanks

you can simple pivot mysql raw to column using case statment — Mahesh Madushanka, Jul 20 '16 at 06:58
@Jens - Sorry that was by mistake i am using sql server 2012 — Pranay Deep, Jul 20 '16 at 06:59
Possible duplicate of [Efficiently convert rows to columns in sql server](http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — Ash, Jul 20 '16 at 07:07
@Ashwin - thanks for replying but can you help me regarding this requirement. — Pranay Deep, Jul 20 '16 at 07:15
That is helping you regarding this requirement. SO is not a code writing service, so you're supposed to that yourself — James Z, Jul 20 '16 at 07:18

score 1 · Answer 1 · answered Jul 20 '16 at 07:21

Whilst the suggested duplicate will get you part of the way there, you actually need to unpivot then pivot, like this.

(Oh and please don't post images. DDL is appreciated and saves us typing and/or guessing.)

CREATE TABLE #Test( Action char, [Key] INT, Old varchar(5), OldValue  varchar(5), New  varchar(5), NewValue  varchar(5));

INSERT INTO #Test VALUES
('U', 123, 'Col1','Dog','Col1','Dog'),
('U', 123, 'Col2','Cat','Col2','Mouse'),
('U', 123, 'Col3','Honey','Col3','Bee'),
('I', 123, NULL,NULL,'Col45','Sun');


SELECT PVT.Action
      ,PVT.[Key]
      ,PVT.OldCol1
      ,PVT.OldCol2
      ,PVT.OldCol3
      ,PVT.NewCol1
      ,PVT.NewCol2
      ,PVT.NewCol3
      ,PVT.NewCol45 FROM (
SELECT [Action]
      ,[Key]
      ,Label
      ,Value 
FROM #Test
CROSS APPLY (VALUES ('Old'+Old, OldValue), ('New'+New, NewValue)) c(label, value)
)src
PIVOT 
(
MAX(VALUE) FOR Label IN (OldCol1, NewCol1, OldCol2, NewCol2, OldCol3, NewCol3, NewCol45)
)PVT
ORDER BY PVT.Action Desc


Action Key         OldCol1 OldCol2 OldCol3 NewCol1 NewCol2 NewCol3 NewCol45
------ ----------- ------- ------- ------- ------- ------- ------- --------
U      123         Dog     Cat     Honey   Dog     Mouse   Bee     NULL
I      123         NULL    NULL    NULL    NULL    NULL    NULL    Sun

(2 row(s) affected)

this is also working but grof1 's solutions was some what near where i was stuck. thanks anyways — Pranay Deep, Jul 20 '16 at 11:26

score 0 · Accepted Answer · answered Jul 20 '16 at 07:22

I guess pivoting will be enough:

SELECT *
FROM (
    SELECT [Action], [Key], 'Old' + Old as [a], OldValue as [Value]
    FROM Table1
    UNION ALL
    SELECT [Action], [Key], 'New' + New, NewValue
    FROM Table1
    ) as p
PIVOT (
    MAX([Value]) FOR [a] IN ([OldCol1],[OldCol2],[OldCol3],[NewCol1],[NewCol2],[NewCol3],[NewCol45])
) as pvt

Output:

Action  Key OldCol1 OldCol2 OldCol3 NewCol1 NewCol2 NewCol3 NewCol45
I       123 NULL    NULL    NULL    NULL    NULL    NULL    Sun
U       123 Dog     Cat     Honey   Dog     Mouse   Bee     NULL

If there are MANY Old and 'New` values then you need dynamic SQL

thanks it is working and now i know where was my mistake. – Pranay Deep Jul 20 '16 at 11:25 — Pranay Deep, Jul 20 '16 at 11:25
Great if my answer helped you! :) – gofr1 Jul 20 '16 at 11:27 — gofr1, Jul 20 '16 at 11:27

Convert Rows Into Columns in new Table

2 Answers2