SQL Data columns to Data Rows

Question

Post reply

SQL Data columns to Data Rows

aarionsql

SSCrazy

Points: 2392
More actions
November 13, 2014 at 8:57 am

#295096

Hi All,
Possibly a simple answer but it has been a long day and I cannot get it working. Any help would be appreciated
Col1, col2,col3
AB , BC ,DA
I want to make it in so that the values in each of the columns are in a new row. Another column will be added with hardcoded text. Example is below:
Values , HardCoded
AB , ABC0
BC , ABC1
DA ,ABC20
Thank you

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

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

g.britton SSChampion Points: 13863 More actions · Answer 1

declare @t table (Col1 char(2), col2 char(2),col3 char(2))

INSERT INTO @t (col1, col2, col3) VALUES

('AB' , 'BC' ,'DA')

SELECT a AS [Values], x.val AS HardCoded

FROM @t

unpivot (a for col in (col1, col2, col3)) u

JOIN (

SELECT 'ab' as col, 'abc0' as val UNION all

SELECT 'bc', 'abc1' UNION ALL

SELECT 'da', 'abc20'

) x ON x.col = a

Gerald Britton, Pluralsight courses

aarionsql SSCrazy Points: 2392 More actions · Answer 2

HI Thank you .. I have amended the code to get it working for getting the data from a table .. many thanks for your help!

DECLARE @t table (Col1 char(25), col2 char(25),col3 char(25))

INSERT INTO @t (col1, col2, col3)

(

SELECT column1,column2,column3

FROM [dbo].[TempAudit]

WHERE

column2 = 'Forecast'

)

SELECT a AS [Values], x.val AS HardCoded

FROM @t

unpivot (a for col in (col1, col2, col3)) u

JOIN (

SELECT (SELECT col1 FROM @t) as col, 'abc0' as val UNION all

SELECT (SELECT col2 FROM @t), 'abc1' UNION ALL

SELECT (SELECT col3 FROM @t), 'abc20'

) x ON x.col = a

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

aarionsql (11/13/2014)
HI Thank you .. I have amended the code to get it working for getting the data from a table .. many thanks for your help!
DECLARE @t table (Col1 char(25), col2 char(25),col3 char(25))
INSERT INTO @t (col1, col2, col3)
(
SELECT column1,column2,column3
FROM [dbo].[TempAudit]
WHERE
column2 = 'Forecast'
)
SELECT a AS [Values], x.val AS HardCoded
FROM @t
unpivot (a for col in (col1, col2, col3)) u
JOIN (
SELECT (SELECT col1 FROM @t) as col, 'abc0' as val UNION all
SELECT (SELECT col2 FROM @t), 'abc1' UNION ALL
SELECT (SELECT col3 FROM @t), 'abc20'
) x ON x.col = a

Probably not a good thing there. Instead of doing a single scan from @t, you've turned it into at least 4.

--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)

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 4

One alternative to the unpivot is to use the Cross apply method.

DECLARE @t table (Col1 char(25), col2 char(25),col3 char(25))

INSERT INTO @t (col1, col2, col3) VALUES

('AB' , 'BC' ,'DA')

SELECT

Cav.Value

,CASE Value

WHEN 'AB' THEN 'abc0'

WHEN 'BC' THEN 'abc1'

WHEN 'DA' THEN 'abc20'

END HardCoded

FROM

(

SELECT

Col1

,Col2

,Col3

FROM

@t

) X

CROSS APPLY

(VALUES

(Col1)

,(Col2)

,(Col3)

) CaV (Value)

This might also make it simpler to code a look up to replace the CASE statement with a JOIN to said look up.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

aarionsql SSCrazy Points: 2392 More actions · Answer 5

Hi Jeff,

Thank you for your thoughts. I have been trying to make it set based but having no luck with the syntax. Do you have any suggestions please.

Thank you

aarionsql SSCrazy Points: 2392 More actions · Answer 6

Jason-299789 (11/14/2014)
One alternative to the unpivot is to use the Cross apply method.
DECLARE @t table (Col1 char(25), col2 char(25),col3 char(25))
INSERT INTO @t (col1, col2, col3) VALUES
('AB' , 'BC' ,'DA')
SELECT
Cav.Value
,CASE Value
WHEN 'AB' THEN 'abc0'
WHEN 'BC' THEN 'abc1'
WHEN 'DA' THEN 'abc20'
END HardCoded
FROM
(
SELECT
Col1
,Col2
,Col3
FROM
@t
) X
CROSS APPLY
(VALUES
(Col1)
,(Col2)
,(Col3)
) CaV (Value)

This might also make it simpler to code a look up to replace the CASE statement with a JOIN to said look up.

HJi Jason,,

Many thanks for this. I will try and give it ago.

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 7

Jason-299789 (11/14/2014)
One alternative to the unpivot is to use the Cross apply method.
DECLARE @t table (Col1 char(25), col2 char(25),col3 char(25))
INSERT INTO @t (col1, col2, col3) VALUES
('AB' , 'BC' ,'DA')
SELECT
Cav.Value
,CASE Value
WHEN 'AB' THEN 'abc0'
WHEN 'BC' THEN 'abc1'
WHEN 'DA' THEN 'abc20'
END HardCoded
FROM
(
SELECT
Col1
,Col2
,Col3
FROM
@t
) X
CROSS APPLY
(VALUES
(Col1)
,(Col2)
,(Col3)
) CaV (Value)

This might also make it simpler to code a look up to replace the CASE statement with a JOIN to said look up.

A minor note,

...

FROM @t

CROSS APPLY (VALUES

(Col1)

,(Col2)

,(Col3)

) CaV (Value)

is enough.

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 8

You are right you don't need the subselect :w00t:, Its just a quirk I have developed in regards to using this method especially when using multi-column tables.

_________________________________________________________________________
SSC Guide to Posting and Best Practices