Columns to Rows

Question

Post reply

Columns to Rows

texpic

SSCertifiable

Points: 5887
More actions
May 12, 2012 at 8:51 pm

#257880

This is a simple example of two table I need to be converted from columns to rows and joined. How do I get there in SQL 2008 (crosstab, pivot??)? Thanks!
CREATE TABLE #TempYesNo
(IdNum VarChar(10),
P01 VarChar(10), P02 VarChar(10),
P03 VarChar(10), P04 VarChar(10),
P05 VarChar(10), P06 VarChar(10))
INSERT INTO #TempYesNo
SELECT '1001', 'Y', 'N', 'Y', 'N', 'Y', 'N'
CREATE TABLE #TempValue
(IdNum VarChar(10),
P01 int, P02 int,
P03 int, P04 int,
P05 int, P06 int)
INSERT INTO #TempValue
SELECT '1001', 10, 0, 20, 0, 30, 0
SELECT * FROM #TempYesNo
SELECT * FROM #TempValue
Need This Result:
IdNum Pnum YN VALUE
1001 P01 Y 10
1001 P02 N 0
1001 P03 Y 20
1001 P04 N 0
1001 P05 Y 30
1001 P06 N 0

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Great Job posting all the necessary information. Third post today i really wanted to have a go at it 🙂 Keep it up.

Here are few solutions:

Method 1:

SELECT IdNum

,PNum = ColName

,YN = SUBSTRING(ColsVals,1,10)

,Value = SUBSTRING(ColsVals,11,19)

FROM

(

SELECT T1.IdNum

, P01 = CAST ( T1.P01 AS CHAR(10)) + CAST ( T2.P01 AS CHAR(19))

, P02 = CAST ( T1.P02 AS CHAR(10)) + CAST ( T2.P02 AS CHAR(19))

, P03 = CAST ( T1.P03 AS CHAR(10)) + CAST ( T2.P03 AS CHAR(19))

, P04 = CAST ( T1.P04 AS CHAR(10)) + CAST ( T2.P04 AS CHAR(19))

, P05 = CAST ( T1.P05 AS CHAR(10)) + CAST ( T2.P05 AS CHAR(19))

, P06 = CAST ( T1.P06 AS CHAR(10)) + CAST ( T2.P06 AS CHAR(19))

FROM #TempYesNo T1

JOIN #TempValue T2

ON T1.IdNum = T2.IdNum

) Pvt_Src

UNPIVOT

(

ColsVals FOR ColName IN ([P01],[P02],[P03],[P04],[P05],[P06])

) Pvt_Handle

Method 2:

; WITH YN AS

(

SELECT IdNum

,PNum = ColName

,YN = ColsVals

FROM #TempYesNo T1

UNPIVOT

(

ColsVals FOR ColName IN ([P01],[P02],[P03],[P04],[P05],[P06])

) Pvt_Handle

)

,Vals AS

(

SELECT IdNum

,PNum = ColName

,Value = ColsVals

FROM #TempValue T1

UNPIVOT

(

ColsVals FOR ColName IN ([P01],[P02],[P03],[P04],[P05],[P06])

) Pvt_Handle

)

SELECT T1.IdNum , T1.PNum , T1.YN , T2.Value

FROM YN T1

JOIN Vals T2

ON T1.IdNum = T1.IdNum

AND T1.PNum = T2.PNum

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

Ya just gotta love non-ansi joins for correlation. 😉

SELECT tyn.IdNum,ca1.Pnum,ca1.YN,ca2.Value

FROM #TempYesNo tyn

INNER JOIN #TempValue tv

ON tyn.IdNum = tv.IdNum

CROSS APPLY (SELECT 'P01', tyn.P01 UNION ALL

SELECT 'P02', tyn.P02 UNION ALL

SELECT 'P03', tyn.P03 UNION ALL

SELECT 'P04', tyn.P04 UNION ALL

SELECT 'P05', tyn.P05 UNION ALL

SELECT 'P06', tyn.P06) ca1 (Pnum,YN)

CROSS APPLY (SELECT 'P01', tv.P01 UNION ALL

SELECT 'P02', tv.P02 UNION ALL

SELECT 'P03', tv.P03 UNION ALL

SELECT 'P04', tv.P04 UNION ALL

SELECT 'P05', tv.P05 UNION ALL

SELECT 'P06', tv.P06) ca2 (Pnum,Value)

WHERE ca1.Pnum = ca2.Pnum

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Heh... I took too long and CC beat me.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

texpic SSCertifiable Points: 5887 More actions · Answer 4

Three different solutions to consider, thank you. The plot thickens on the number of tables and some curve balls on the joins, table not exactly the same.

-- 3 tables now

-- Added date for all tables and rule field for 2 tables

-- #TempValue does not need rule field, data same for all rules

-- DROP TABLE #TempYesNo DROP TABLE #TempValue DROP TABLE #TempValueLevel

CREATE TABLE #TempYesNo

(IdNum VarChar(10), RepDate VarChar(10), IdRule varchar(10),

P01 VarChar(10), P02 VarChar(10),

P03 VarChar(10), P04 VarChar(10),

P05 VarChar(10), P06 VarChar(10))

INSERT INTO #TempYesNo

SELECT '1001', '20120511', 'xx1', 'Y', 'N', 'Y', 'N', 'Y', 'N' UNION ALL

SELECT '1001', '20120511', 'xx2', 'Y', 'N', 'Y', 'N', 'Y', 'N' UNION ALL

SELECT '1001', '20120512', 'xx1', 'Y', 'N', 'Y', 'N', 'Y', 'N' UNION ALL

SELECT '1002', '20120511', 'xx1', 'Y', 'N', 'Y', 'N', 'Y', 'N'

CREATE TABLE #TempValue

(IdNum VarChar(10), RepDate VarChar(10),

P01a int, P02a int,

P03a int, P04a int,

P05a int,

P01b int, P02b int,

P03b int, P04b int)

INSERT INTO #TempValue

SELECT '1001', '20120511', 10, 0, 20, 0, 30, 100, 0, 200, 0 UNION ALL

SELECT '1001', '20120512', 10, 0, 20, 0, 30, 100, 0, 200, 0 UNION ALL

SELECT '1002', '20120511', 10, 0, 20, 0, 30, 100, 0, 200, 0

CREATE TABLE #TempValueLevel

(IdNum VarChar(10), RepDate VarChar(10), IdRule varchar(10),

P01a int, P02a int,

P03a int, P04a int,

P05a int,

P01b int, P02b int,

P03b int, P04b int)

INSERT INTO #TempValueLevel

SELECT '1001', '20120511', 'xx1', 10, 0, 20, 0, 30, 100, 0, 200, 0 UNION ALL

SELECT '1001', '20120511', 'xx2', 10, 0, 20, 0, 30, 100, 0, 200, 0 UNION ALL

SELECT '1001', '20120512', 'xx1', 10, 0, 20, 0, 30, 100, 0, 200, 0 UNION ALL

SELECT '1002', '20120511', 'xx1', 10, 0, 20, 0, 30, 100, 0, 200, 0

SELECT * FROM #TempYesNo

SELECT * FROM #TempValue

SELECT * FROM #TempValueLevel

texpic SSCertifiable Points: 5887 More actions · Answer 5

Desired result:

--pulling result for one IdNum, one RepDate and one IdRule

DECLARE @IdNum varchar(10)

SET @IdNum = '1001'

DECLARE @RepDate varchar(10)

SET @IdNum = '20120511'

DECLARE @IdRule varchar(10)

SET @IdRule = 'xx1'

Result:

IdNum Date Rule Pnum YN Va Vb

1001 20120511 xx1 P01 Y 10 100

1001 20120511 xx1 P02 N 0 0

1001 20120511 xx1 P03 Y 20 200

1001 20120511 xx1 P04 N 0 0

1001 20120511 xx1 P05 Y 30

1001 20120511 xx1 P06 N

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

Now that you've been shown the principle of how it works, how about showing us your try?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

texpic SSCertifiable Points: 5887 More actions · Answer 7

I'm going to work on it in a bit, will probably do it in a few steps using temp tables then join.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

I don't know if it's possible for you to make changes to the tables or not, but you might want to consider normalizing them a bit. Having to unpivot all the tables to produce joined information is a pretty good indication that the tables need to be normalized.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)