unpivot note

Question

unpivot note

flyingrockfishguy

Newbie

Points: 8
More actions
June 10, 2011 at 4:16 pm

#251840

I made a small syntax error in this query, and would have expected an error when I tried to run it. The results are quite interesting.
SELECT ExtraValue,VendorID
Employee, Orders
FROM
(SELECT 'extra value' as ExtraValue, 1 as VendorID, 'e1' as Emp1, 'e2' as Emp2, 'e3' as Emp3, 'e4' as Emp4, 'e5' as Emp5
) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
) AS unpvt

Viewing 3 posts - 1 through 3 (of 3 total)

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

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 1

flyingrockfishguy (6/10/2011)
I made a small syntax error in this query, and would have expected an error when I tried to run it. The results are quite interesting.
SELECT ExtraValue,VendorID
Employee, Orders
FROM
(SELECT 'extra value' as ExtraValue, 1 as VendorID, 'e1' as Emp1, 'e2' as Emp2, 'e3' as Emp3, 'e4' as Emp4, 'e5' as Emp5
) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
) AS unpvt

Any chance of you telling us what the syntax error you made is so we don't have to look for it? 😉

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

WayneS SSC Guru Points: 95461 More actions · Answer 2

flyingrockfishguy (6/10/2011)
I made a small syntax error in this query, and would have expected an error when I tried to run it. The results are quite interesting.
SELECT ExtraValue,VendorID
Employee, Orders
FROM
(SELECT 'extra value' as ExtraValue, 1 as VendorID, 'e1' as Emp1, 'e2' as Emp2, 'e3' as Emp3, 'e4' as Emp4, 'e5' as Emp5
) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
) AS unpvt

Do you mean the missing comma at the end of the first line? You ended up aliasing the "VendorID" column as Employee (remember that the AS keyword is optional). There wasn't a syntax error with that, so SQL just went and did what you told it to.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2