x

question on unpivoting the table

hello can anyone tell me the code to unpivot the table

more ▼

asked Feb 06 '10 at 07:19 AM in Default

java gravatar image

java
9 4 4 4

to unpivot which table? I think you will need to be more specific with your question to get an answer that will help you in detail.
Feb 06 '10 at 08:55 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

It would really help if you provided the dataset that you want to UNPIVOT but here is a simple example for this set of data...

STEP1

Which is generated with the following CTE SQL...

WITH aDataSet
AS
(
    SELECT 12345 [ProductID],
        62393.57 [2008SalesTotals],
        71141.45 [2009SalesTotals]
    UNION ALL
    SELECT 67890, 70996.23, 54378.33
    UNION ALL
    SELECT 23456, 98734.10, 53784.65
    UNION ALL
    SELECT 98765, 75893.54, 98756.54
    UNION ALL
    SELECT 98357, 42890.55, 63489.54
)

The UNPIVOT command would then look something like...

SELECT ProductID,
    SaleYear,
    SaleTotal
FROM
(
    SELECT ProductID,
        [2008SalesTotals] [2008],
        [2009SalesTotals] [2009]
    FROM aDataSet
) AS udt

UNPIVOT
(
    SaleTotal FOR SaleYear
    IN ([2008], [2009])
) AS upvt

This would output a resultset like so...

Result

I hope that helps you out.

more ▼

answered Feb 10 '10 at 07:34 AM

Ian Roke gravatar image

Ian Roke
1.7k 29 33 34

(comments are locked)
10|1200 characters needed characters left

The first place to look is BOL - PIVOT and UNPIVOT are explained here : http://msdn.microsoft.com/en-us/library/ms177410.aspx

more ▼

answered Feb 06 '10 at 08:53 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.3k 73 77 107

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x977
x76

asked: Feb 06 '10 at 07:19 AM

Seen: 874 times

Last Updated: Feb 06 '10 at 08:51 AM