How do I convert column data into row data?

Question

How do I convert column data into row data?

gregorykearney

Old Hand

Points: 304
More actions
September 3, 2013 at 9:23 pm

#279418

table gennum
tollnum, n1, n2, n3
800123, 1234, 1235, 1236
999123, 9876, 9875, 9874
I want to my data to look like:
tollnum,code
800123, 1234
800123, 1235
800123, 1236
999123, 9876
999123, 9875
999123, 9874
How do I convert column data into row data?

Viewing 15 posts - 1 through 15 (of 17 total)

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

PiMané SSChampion Points: 13965 More actions · Answer 1

gregorykearney (9/3/2013)
table gennum
tollnum, n1, n2, n3
800123, 1234, 1235, 1236
999123, 9876, 9875, 9874
I want to my data to look like:
tollnum,code
800123, 1234
800123, 1235
800123, 1236
999123, 9876
999123, 9875
999123, 9874
How do I convert column data into row data?

Hi,

You can use the UNPIVOT statment:

SELECT tollnum,

code

FROM (SELECT *

FROM gennum) p

UNPIVOT

(code

FOR n IN ( n1, n2, n3 ) ) AS unpvt

ORDER BY tollnum, n

If you need to work better, try working less...

PiMané SSChampion Points: 13965 More actions · Answer 2

gregorykearney (9/3/2013)
table gennum
tollnum, n1, n2, n3
800123, 1234, 1235, 1236
999123, 9876, 9875, 9874
I want to my data to look like:
tollnum,code
800123, 1234
800123, 1235
800123, 1236
999123, 9876
999123, 9875
999123, 9874
How do I convert column data into row data?

Or a simple UNION ALL

SELECT * FROM (

SELECT tollnum, n1 as N FROM gennum

UNION ALL

SELECT tollnum, n2 FROM lixo

UNION ALL

SELECT tollnum, n3 FROM lixo

) t

ORDER BY tollnum

Either way it works...

If you need to work better, try working less...

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Or CROSS APPLY VALUES:

-- sample data

;WITH gennum (tollnum, n1, n2, n3) AS (

SELECT 800123, 1234, 1235, 1236 UNION ALL

SELECT 999123, 9876, 9875, 9874

)

-- solution

SELECT g.tollnum, d.code

FROM gennum g

CROSS APPLY (VALUES (n1), (n2), (n3)) d (code)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

PiMané SSChampion Points: 13965 More actions · Answer 4

ChrisM@Work (9/4/2013)
Or CROSS APPLY VALUES:
-- sample data
;WITH gennum (tollnum, n1, n2, n3) AS (
SELECT 800123, 1234, 1235, 1236 UNION ALL
SELECT 999123, 9876, 9875, 9874
)
-- solution
SELECT g.tollnum, d.code
FROM gennum g
CROSS APPLY (VALUES (n1), (n2), (n3)) d (code)

Didn't know this one 🙂

One more for the "bag" 🙂

Nice.

If you need to work better, try working less...

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

PiMané (9/4/2013)
ChrisM@Work (9/4/2013)
Or CROSS APPLY VALUES:
-- sample data
;WITH gennum (tollnum, n1, n2, n3) AS (
SELECT 800123, 1234, 1235, 1236 UNION ALL
SELECT 999123, 9876, 9875, 9874
)
-- solution
SELECT g.tollnum, d.code
FROM gennum g
CROSS APPLY (VALUES (n1), (n2), (n3)) d (code)
Didn't know this one 🙂
One more for the "bag" 🙂
Nice.

Dwain C has a nice article [/url]on it, Ped 😉

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

gregorykearney Old Hand Points: 304 More actions · Answer 6

gregorykearney

Old Hand

Points: 304

September 4, 2013 at 2:50 pm

#1647503

Great working examples, thanks for the education!

Dwain Camps SSC Guru Points: 86908 More actions · Answer 7

PiMané (9/4/2013)
ChrisM@Work (9/4/2013)
Or CROSS APPLY VALUES:
-- sample data
;WITH gennum (tollnum, n1, n2, n3) AS (
SELECT 800123, 1234, 1235, 1236 UNION ALL
SELECT 999123, 9876, 9875, 9874
)
-- solution
SELECT g.tollnum, d.code
FROM gennum g
CROSS APPLY (VALUES (n1), (n2), (n3)) d (code)
Didn't know this one 🙂
One more for the "bag" 🙂
Nice.

And after all the work I've done trying to get the word out too! [face-to-palm]

Thanks for the plug Chris!

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

dwain.c (9/11/2013)
PiMané (9/4/2013)
ChrisM@Work (9/4/2013)
Or CROSS APPLY VALUES:
-- sample data
;WITH gennum (tollnum, n1, n2, n3) AS (
SELECT 800123, 1234, 1235, 1236 UNION ALL
SELECT 999123, 9876, 9875, 9874
)
-- solution
SELECT g.tollnum, d.code
FROM gennum g
CROSS APPLY (VALUES (n1), (n2), (n3)) d (code)
Didn't know this one 🙂
One more for the "bag" 🙂
Nice.
And after all the work I've done trying to get the word out too! [face-to-palm]
Thanks for the plug Chris!

I felt sorry for you tramping the streets of Port Moresby with that heavy sandwich board :hehe:

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 9

ChrisM@Work (9/4/2013)
Or CROSS APPLY VALUES

Somewhat odd to use CROSS APPLY here. The normal is CROSS JOIN. Of course since there is no correlation on the right side, the CROSS APPLU is effectively a CROSS JOIN, but nevertheless.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Luis Cazares SSC Guru Points: 183698 More actions · Answer 10

Erland Sommarskog (9/11/2013)
ChrisM@Work (9/4/2013)
Or CROSS APPLY VALUES
Somewhat odd to use CROSS APPLY here. The normal is CROSS JOIN. Of course since there is no correlation on the right side, the CROSS APPLU is effectively a CROSS JOIN, but nevertheless.

Are you sure? CROSS JOIN shows an error because it can't referenciate n1, n2 and n3.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

Erland Sommarskog (9/11/2013)
ChrisM@Work (9/4/2013)
Or CROSS APPLY VALUES
Somewhat odd to use CROSS APPLY here. The normal is CROSS JOIN. Of course since there is no correlation on the right side, the CROSS APPLU is effectively a CROSS JOIN, but nevertheless.

There are similarities and differences to CROSS JOIN. All rows from the RHS are matched to all rows on the LHS, for instance. However, whilst the VALUES clause of a CAV block introduces a table source such as CROSS JOIN requires, the values are taken from the "current row" on the LHS, from any table source in scope.

(CROSS) APPLY without introducing a table source effectively translates to "calculate new columns" using the current row(s). VALUES expands on this functionality allowing you to create new rows as well. Correlation is moot - you play with the data already in scope.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 12

Ah, I see. I had just completed an unpivot solution (using CROSS JOIN) in another thread prior to reading this thread. I need to look into this idea more.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

Dwain C has a nice article covering usage, I've put a link in an earlier post in this thread.

Blimey. I've learned so much from your website over the years. Nice - and a privilege - to see the tables turned.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Dwain Camps SSC Guru Points: 86908 More actions · Answer 14

ChrisM@Work (9/12/2013)
Blimey. I've learned so much from your website over the years. Nice - and a privilege - to see the tables turned.

I would have to second that.

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