none
sql query

    Question


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

    Saturday, October 19, 2013 4:22 PM

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
    Saturday, October 19, 2013 4:44 PM

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


    Saturday, October 19, 2013 4:40 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
    Saturday, October 19, 2013 4:44 PM