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 (https://blog.sqlauthority.com)
12 Comments. Leave new
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
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
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
SELECT [SUBJECT] , FA1 , FA2 FROM FAPIVOT
PIVOT (SUM(MARKS) FOR FAE IN (FA1 , FA2))
AS TBLPIVOT
ABOVE IS THE QUERY
TABLE X (A,B,C) & TABLE Y(A,B,C)
data: TABLE X(1,2,3) , TABLE Y(2,3,4)
PIVOT looks like
X Y
————
A 1 2
B 2 3
C 3 4
Hi required output in following format
INPUT
X Y
1 1
2 2
3 3
4 4
OUTPUT
X 1
X 2
X 3
X 4
Y 1
Y 2
Y 3
Y 4
Why do you want to get this output? You can easily do this in a front end application
Anyway if you need answer, you can try this
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
Whats the point of putting an image instead of the code, just in case you want to test or play around?
My Bad, the code is also there. thanks for the article
The first part is text which you can copy. Only the second part is image