sql query

Question

Convert Rows to Columns, Columns to Rows in SQL Server with out using PIVOT and UNPIVOT

Answer 1

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

Answer 2

(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