How to convert some columns into rows with the first two columns still retained as columns

Question

Post reply

How to convert some columns into rows with the first two columns still retained as columns

rmelgarca

Valued Member

Points: 53
More actions
March 8, 2012 at 6:09 pm

#269797

Hi,
Can PIVOT do the above function? I have the following table:
SELECT [Column1]
,[Column2]
,[REF001]
,[REF002]
,[REF003]
,[REF004]
,[REF005]
,[REF006]
FROM [SourceTable]
The result is:
Column1Column2REF001REF002REF003REF004REF005REF006
1 A 1 2 3 4 5 6
I want the result to be displayed as
Column1 Column2 RefName RefValue
1 A REF001 1
1 A REF002 2
1 A REF003 3
1 A REF004 4
1 A REF005 5
1 A REF006 6
Please help.
Thank you.

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

select

Column1,

Column2,

'REF001' as RefName,

REF001 as RefValue

from

SourceTable

union all

select

Column1,

Column2,

'REF002' as RefName,

REF002 as RefValue

from

SourceTable

union all

select

Column1,

Column2,

'REF003' as RefName,

REF003 as RefValue

from

SourceTable

union all

select

Column1,

Column2,

'REF004' as RefName,

REF004 as RefValue

from

SourceTable

union all

select

Column1,

Column2,

'REF005' as RefName,

REF005 as RefValue

from

SourceTable

union all

select

Column1,

Column2,

'REF006' as RefName,

REF006 as RefValue

from

SourceTable;

rmelgarca Valued Member Points: 53 More actions · Answer 2

Thank you.

It is simple if the columns are less than 10. But what if the columns REF* are 100 or more?

Is there a better way of coding?

rmelgarca Valued Member Points: 53 More actions · Answer 3

I was hoping that a pivot command will be able to do that.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

At the very least, you'd have to list all of the column names in the UNPIVOT.

I believe I know a way to do this fairly easily. If you'd post a sample table and some "readily consumable" data for me to use as an example, I can probably demonstrate it. Please see the first link in my signature line below for how to post the data so that it's "readily consumable". Thanks.

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

rmelgarca Valued Member Points: 53 More actions · Answer 5

Thank you Jeff.

I was able to use the unpivot command but only with one column not translated.

What I did after that was to join it to the original table to retrieve the other field.

Dwain Camps SSC Guru Points: 86948 More actions · Answer 6

Another option:

DECLARE @data TABLE (C1 INT, C2 CHAR(1), REF001 INT, REF002 INT

,REF003 INT, REF004 INT, REF005 INT, REF006 INT)

INSERT @data

VALUES (1, 'A', 1, 2, 3, 4, 5, 6)

SELECT [C1],[C2],[C3] As RefName

,CASEWHEN [C3] = 'REF001' THEN [REF001]

WHEN [C3] = 'REF002' THEN [REF002]

WHEN [C3] = 'REF003' THEN [REF003]

WHEN [C3] = 'REF004' THEN [REF004]

WHEN [C3] = 'REF005' THEN [REF005]

ELSE [REF006] END AS RefValue

FROM @data

CROSS APPLY (SELECT 'REF001' As C3

UNION ALL SELECT 'REF002'

UNION ALL SELECT 'REF003'

UNION ALL SELECT 'REF004'

UNION ALL SELECT 'REF005'

UNION ALL SELECT 'REF006') x

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

rmelgarca (3/8/2012)
Thank you Jeff.
I was able to use the unpivot command but only with one column not translated.
What I did after that was to join it to the original table to retrieve the other field.

That's why I asked you to post some data in a readily consumable fashion so that I can show you the easy way to do a whole lot more columns and how to include 2 "static" columns easily.

Dwain is on the right track but the code can be simplified quite a bit and made a bit faster especially if you have 100 columns. I can also show you how to NOT pivot any null data if that also might be a requirement.

--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: 1004356 More actions · Answer 8

Hmmm... Ok, moving on. 😉

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

rmelgarca Valued Member Points: 53 More actions · Answer 9

Hi Jeff,

Sorry for the late reply. I was involved in another project and was not able to come back to this.

And thank you for your eagerness to help.

I was able to solve the problem by reducing the retention from two to one column.

Regards.