looking for way to unpivot a table, and make field name one of the columns in the output

Question

looking for way to unpivot a table, and make field name one of the columns in the output

nonghead-webspam

SSCommitted

Points: 1935
More actions
January 15, 2014 at 11:54 am

#281243

I have a table:
Part# | CustomerID | Wk1 | Wk2 | .......... | Wk52
I want a resulting table that has:
Part# | CustomerID | Wk_Num | Qty
What is the most optimized way to do this, and that would be easy enough to maintain later (i.e. less experienced folks trying to maintain)? I realize those two might not go together. So, maybe some options. Optimized is good if I only get one.
I tried Unpivot, but don't know how to get that Wk_Num in there from the Wk* column names.
I know "select / Union All" would be simple and give me ultimate control, but, seems like that's going to run 52 queries instead of one.

Viewing 14 posts - 1 through 13 (of 13 total)

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

nonghead-webspam SSCommitted Points: 1935 More actions · Answer 1

And I just found "select / union all" will only work up to 19 iterations, and after that it starts saying it can't find columns.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

You can try UNPIVOT or CROSS APPLY VALUES[/url]

It really depends on what will work for you.

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

nonghead-webspam SSCommitted Points: 1935 More actions · Answer 3

As I said, I tried unpivot, but didn't see a way to get the week_num value in there from the wk# column name.

I'll look at cross apply.

nonghead-webspam SSCommitted Points: 1935 More actions · Answer 4

also, to whomever, some code examples regarding exactly what I'm trying to do would be helpful.

Thanks.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

For unpviot, you might need to use a substring to get the week number and then cast it to the correct data type.

I have nothing to test, but it might look like this:

SELECT Part#,

CustomerID,

SUBSTRING( Wk_Num, 3, 2) Wk_Num,

Qty

FROM (

SELECT Part#,

CustomerID,

Wk1,

Wk2,

Wk3,

Wk4,

Wk52

FROM SomeTable) p

UNPIVOT

(Qty FOR Wk_Num IN

(Wk1, Wk2, Wk3, Wk4, Wk52)

)AS unpvt;

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

nonghead-webspam (1/15/2014)
also, to whomever, some code examples regarding exactly what I'm trying to do would be helpful.
Thanks.

Code examples are available in the links I provided. To get code examples that work fine with your data, you should post DDL and sample data. I forgot to mention it, but it will get you better answers if you do. For information on how to do it, check the link in my signature.

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

ScottPletcher SSC Guru Points: 101098 More actions · Answer 7

Here is a sample CROSS APPLY for your situation.

SELECT Part#, CustomerID, ca1.Wk_Num, ca1.Qty

FROM (

SELECT 'A' AS Part#, 1 AS CustomerID, 10 AS Wk1, 20 AS Wk2, 30 AS Wk3

) AS test_data

CROSS APPLY (

SELECT 1 AS Wk_Num, Wk1 AS Qty UNION ALL

SELECT 2, Wk2 UNION ALL

SELECT 3, Wk3 --UNION ALL

--add other wks here

) AS ca1

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

nonghead-webspam SSCommitted Points: 1935 More actions · Answer 8

Thanks Luis. I'm seeing how the lack of data samples is not helping.

I'll try what you posted. Sorry, I didn't think to look at links. I just thought they were part of your signature or something. Bottom line, I didn't see them!

Ok, just looked. I see what you mean.

nonghead-webspam SSCommitted Points: 1935 More actions · Answer 9

Thanks Scott. Not sure that would quite work for my data, but since I didn't post any data, how would anyone know that.

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

Here's an example using the VALUES clause instead of UNION ALLs on the CROSS APPLY approach.

SELECT Part#, CustomerID, ca1.Wk_Num, ca1.Qty

FROM MyTable

CROSS APPLY (

VALUES(1,Wk1),

(2,Wk2),

(3,Wk3),

(4,Wk4),

--...

(52,Wk52)

) AS ca1(Wk_Num, Qty)

Don't worry about not posting sample data as this was your first post, but be sure to do it in the future (or in here if you still can't find out how to make it work). 😉

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 11

nonghead-webspam (1/15/2014)
And I just found "select / union all" will only work up to 19 iterations, and after that it starts saying it can't find columns.

I just tried to reproduce this error, but it doesn't seem to happen on my instance and I can't find any documentation on why it would fail in any way.

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

nonghead-webspam SSCommitted Points: 1935 More actions · Answer 12

That one looks pretty slick. I'll try that.

I made do with a stored proc and dynamic sql that loops through an insert statement, but, it's not cooperating right now.

nonghead-webspam SSCommitted Points: 1935 More actions · Answer 13

Luis Cazares (1/15/2014)

Here's an example using the VALUES clause instead of UNION ALLs on the CROSS APPLY approach.

SELECT Part#, CustomerID, ca1.Wk_Num, ca1.Qty

FROM MyTable

CROSS APPLY (

VALUES(1,Wk1),

(2,Wk2),

(3,Wk3),

(4,Wk4),

--...

(52,Wk52)

) AS ca1(Wk_Num, Qty)

This one is getting me there, and it does not look arcane to adapt. Thanks a lot.