SQL SERVER – UNPIVOT Table Example

My previous article SQL SERVER – PIVOT Table Example encouraged few of my readers to ask me question about UNPIVOT table. UNPIVOT table is reverse of PIVOT Table.

USE AdventureWorks
GO
CREATE TABLE #Pvt ([CA] INT NOT NULL,
[AZ] INT NOT NULL,
[TX] INT NOT NULL);
INSERT INTO #Pvt ([CA], [AZ], [TX])
SELECT [CA], [AZ], [TX]
FROM
(
SELECT sp.StateProvinceCode
FROM Person.Address a
INNER JOIN Person.StateProvince sp
ON a.StateProvinceID = sp.StateProvinceID
) p
PIVOT
(
COUNT (StateProvinceCode)
FOR StateProvinceCode
IN ([CA], [AZ], [TX])
)
AS pvt;
SELECT StateProvinceCode, Customer_Count
FROM
(
SELECT [CA], [AZ], [TX]
FROM #Pvt
) t
UNPIVOT
(
Customer_Count
FOR StateProvinceCode
IN ([CA], [AZ], [TX])
)
AS unpvt;
DROP TABLE #Pvt;
GO

Reference : Pinal Dave (http://blog.SQLAuthority.com)

13 thoughts on “SQL SERVER – UNPIVOT Table Example

  1. Pingback: SQL SERVER - PIVOT and UNPIVOT Table Examples Journey to SQL Authority with Pinal Dave

  2. Pingback: SQL SERVER - 2008 - Interview Questions and Answers - Part 8 Journey to SQL Authority with Pinal Dave

  3. Pingback: SQL SERVER - Example of PIVOT UNPIVOT Cross Tab Query in Different SQL Server Versions Journey to SQL Authority with Pinal Dave

  4. this is the original data

    Loc_Id doctorName Qualification Dept
    ——- ————– ————- ——
    1 abc M.B.B.S. Op
    2 xyz D.H.M.S. DP
    3 ppt H.M. CP

    I want to convert it like bellow

    Loc1 Loc2 Loc3
    ——– ———- ——-
    abc xyz ppt
    M.B.B.S. D.H.M.S. H.M.
    OP DP CP

    please help me

    Like

  5. I’m a little confused about your example here. Why go to all this trouble when you could just write this and get the same results.

    Select sp.StateProvinceCode
    ,Count(*) as Customer_count
    From Person.Address a
    Inner join Person.StateProvince sp
    On a.StateProvinceID = sp.StateProvinceID
    Where sp.StateProvinceCode in (‘CA’, ‘AZ’, ‘TX’)
    Group by sp.StateProvinceCode

    Like

  6. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 18 of 31 Journey to SQLAuthority

  7. Hi Pinal, this is of great use.

    But i do have 1 more query that if i have a temp table where rows can have any data and i need to pivot that.
    For example i have a output like

    Description Subject Marks
    FA1 Sub1 10
    FA1 Sub2 15
    FA1 Sub3 11
    FA1 Sub4 12
    FA2 Sub1 9
    FA2 Sub2 8
    FA2 Sub3 12
    FA2 Sub4 10

    I need output like

    Subject FA1 FA2
    Sub1 10 9
    Sub2 15 8
    Sub3 11 12
    Sub4 12 10

    Then how can i achieve this, as Description can be more then that

    Thanks
    Sumit Gupta

    Like

  8. Hi

    This is my store procedure. I need to store the value into table
    There is a error

    Msg 213, Level 16, State 1, Procedure usp_pivot_by_Date_Seats, Line 7
    Insert Error: Column name or number of supplied values does not match table definition.

    But Column and number of column are in order. Below is Store porcedure

    USE [SpectrumDB]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[usp_pivot_by_Date_Seats]
    –@WeekNo Numeric
    AS
    BEGIN
    SET NOCOUNT ON;

    insert into tbl_Days_TEMP

    select * from ( select Title,Qty,WeekName from dbo.vw_RPT_Pivot_Weekly_Seat) DataTable
    PIVOT (SUM(Qty) FOR [weekname] IN ([Sunday],[Monday],[Tuesday],[Wednesday],[Thursday],
    [Friday],[Saturday])) PivotTable

    END

    Table tbl_Days_Temp is below

    USE [SpectrumDB]
    GO
    /****** Object: Table [dbo].[tbl_Days_TEMP] Script Date: 03/07/2013 21:49:22 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[tbl_Days_TEMP](
    [Title] [nvarchar](500) NULL,
    [WeekName] [nvarchar](50) NULL,
    [Value1] [numeric](18, 0) NULL CONSTRAINT [DF_tbl_Days_TEMP_Value1] DEFAULT ((0)),
    [Value2] [numeric](18, 0) NULL CONSTRAINT [DF_tbl_Days_TEMP_Value2] DEFAULT ((0)),
    [Value3] [numeric](18, 0) NULL CONSTRAINT [DF_tbl_Days_TEMP_Value3] DEFAULT ((0)),
    [Value4] [numeric](18, 0) NULL CONSTRAINT [DF_tbl_Days_TEMP_Value4] DEFAULT ((0)),
    [Value5] [numeric](18, 0) NULL CONSTRAINT [DF_tbl_Days_TEMP_Value5] DEFAULT ((0)),
    [Value6] [numeric](18, 0) NULL CONSTRAINT [DF_tbl_Days_TEMP_Value6] DEFAULT ((0)),
    [Value7] [numeric](18, 0) NULL CONSTRAINT [DF_tbl_Days_TEMP_Value7] DEFAULT ((0)),
    [Qty] [numeric](18, 0) NULL
    ) ON [PRIMARY]

    Pls help me where i did wrong

    Like

  9. Pingback: SQL SERVER – Weekly Series – Memory Lane – #031 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s