Answered by:
sql query

Question
Answers
-
(1, 'Date', '01/01/2013'), (1, 'InvoiceNbr', '123'), (1, 'Amount', '34.75'), (2, 'Date', '01/01/2013'), (2, 'InvoiceNbr', '17'); Select pvt.DataKey, Max(Case When pvt.DataType = 'Date' Then pvt.DataValue End) As Date, Max(Case When pvt.DataType = 'InvoiceNbr' Then pvt.DataValue End) As InvoiceNbr, Max(Case When pvt.DataType = 'Amount' Then pvt.DataValue End) As Amount From @PivotDemo pvt Group By pvt.DataKey; Declare @UnPivotDemo Table(DataKey int Primary Key, Date varchar(20), InvoiceNbr varchar(20), Amount varchar(20)); Insert @UnPivotDemo(DataKey, Date, InvoiceNbr, Amount) Values (1, '01/01/2013', '123', '34.75'), (2, '01/01/2013', '17', NULL); Select u.DataKey, Case When n.Number = 1 Then 'Date' When n.Number = 2 Then 'InvoiceNbr' When n.Number = 3 Then 'Amount' Else Null End As DataType, Case When n.Number = 1 Then u.Date When n.Number = 2 Then u.InvoiceNbr When n.Number = 3 Then u.Amount Else Null End As DataValue From @UnPivotDemo u Cross Join (Select 1 As Number Union All Select 2 Union All Select 3) As n Order By u.DataKey, n.Number;
Tom- Proposed as answer by SathyanarrayananSModerator Monday, October 21, 2013 2:07 AM
- Marked as answer by Allen Li - MSFTModerator Sunday, October 27, 2013 10:32 AM
All replies
-
Refer the below example,
CREATE TABLE [dbo].[unpivotTable]([Order Number] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Order Date] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Quantity] [int],
[Row Id] [int]
) ON [PRIMARY]Insert into unpivotTable values('111','Jan-2013',30,1)
Insert into unpivotTable values('222','Jan-2013',40,2)
Insert into unpivotTable values('333','Feb-2013',50,3)
Insert into unpivotTable values('444','Mar-2013',60,4)
---------------------------------
Declare @strs nvarchar(4000),@orderdate nvarchar(10)
Declare C Cursor for Select distinct [Order Date] from unpivotTable order by [Order Date]
set @strs = ''
Open C
Fetch next from C into @orderdate
While @@fetch_status=0
Begin
declare @quantitysum int
set @quantitysum=0
select @quantitysum=sum([Quantity]) from unpivotTable where [Order Date]=@orderdate
set @strs = @strs + '''' + cast(@quantitysum as nvarchar(5)) + ''' as [' + @orderdate + '],'
Fetch next from C into @orderdate
End
Close C
Deallocate C
set @strs='Select ' + left(@strs,len(@strs)-1)
exec(@strs)Regards, RSingh
- Edited by Ch. Rajen Singh Saturday, October 19, 2013 4:43 PM
-
(1, 'Date', '01/01/2013'), (1, 'InvoiceNbr', '123'), (1, 'Amount', '34.75'), (2, 'Date', '01/01/2013'), (2, 'InvoiceNbr', '17'); Select pvt.DataKey, Max(Case When pvt.DataType = 'Date' Then pvt.DataValue End) As Date, Max(Case When pvt.DataType = 'InvoiceNbr' Then pvt.DataValue End) As InvoiceNbr, Max(Case When pvt.DataType = 'Amount' Then pvt.DataValue End) As Amount From @PivotDemo pvt Group By pvt.DataKey; Declare @UnPivotDemo Table(DataKey int Primary Key, Date varchar(20), InvoiceNbr varchar(20), Amount varchar(20)); Insert @UnPivotDemo(DataKey, Date, InvoiceNbr, Amount) Values (1, '01/01/2013', '123', '34.75'), (2, '01/01/2013', '17', NULL); Select u.DataKey, Case When n.Number = 1 Then 'Date' When n.Number = 2 Then 'InvoiceNbr' When n.Number = 3 Then 'Amount' Else Null End As DataType, Case When n.Number = 1 Then u.Date When n.Number = 2 Then u.InvoiceNbr When n.Number = 3 Then u.Amount Else Null End As DataValue From @UnPivotDemo u Cross Join (Select 1 As Number Union All Select 2 Union All Select 3) As n Order By u.DataKey, n.Number;
Tom- Proposed as answer by SathyanarrayananSModerator Monday, October 21, 2013 2:07 AM
- Marked as answer by Allen Li - MSFTModerator Sunday, October 27, 2013 10:32 AM