September 3, 2013 at 9:23 pm
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?
September 4, 2013 at 7:16 am
gregorykearney (9/3/2013)
table gennumtollnum, 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
September 4, 2013 at 7:19 am
gregorykearney (9/3/2013)
table gennumtollnum, 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...
September 4, 2013 at 7:46 am
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)
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
September 4, 2013 at 8:01 am
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.
September 4, 2013 at 8:06 am
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 😉
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
September 4, 2013 at 2:50 pm
Great working examples, thanks for the education!
September 11, 2013 at 3:54 am
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 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
September 11, 2013 at 4:00 am
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:
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
September 11, 2013 at 3:53 pm
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]
September 11, 2013 at 4:37 pm
Erland Sommarskog (9/11/2013)
ChrisM@Work (9/4/2013)
Or CROSS APPLY VALUESSomewhat 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.
September 12, 2013 at 1:19 am
Erland Sommarskog (9/11/2013)
ChrisM@Work (9/4/2013)
Or CROSS APPLY VALUESSomewhat 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.
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
September 12, 2013 at 1:21 am
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]
September 12, 2013 at 1:23 am
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.
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
September 12, 2013 at 3:07 am
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 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
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply