none
how to transpose column into row

    Question

  • Hi,

    I need to transpose column into row anyone can help me.

    Eg:

    Name Quantity Date
    RM1 100 20-06-2013
    RM2 150 20-06-2013
    RM3 200 20-06-2013
    RM4 140 20-06-2013

    if i execute my SP i get data like the above but i need to get like below table.

    Name 20-06-2013 21-06-2013 upto N Date
    RM1 100 120
    RM2 150 100
    RM3 200 150

    RM4

    The Date should be dynamic from

    140

    100

    • Moved by Bob BeaucheminMVP Sunday, June 23, 2013 6:06 PM Moved to a more relevent forum, best solved w/T-SQL
    Sunday, June 23, 2013 4:50 AM

Answers

  • Sunday, June 23, 2013 10:56 PM
  • If there was only one date value, and it was known, this would be a way:

    use tempdb;
    create table T1 (Name varchar(50), Quantity int, [Date] date);
    
    set dateformat dmy;
    insert into T1 values
     ('RM1',100,'20-06-2013'),
     ('RM2',150,'20-06-2013'),
     ('RM3',200,'20-06-2013'),
     ('RM4',140,'20-06-2013');
    
    select Name, [20-06-2013]
      from T1
      pivot (sum(Quantity) 
             for [Date] in ([20-06-2013])) as P;


    But, considering that the date values ​​are dynamic:

    create table T2 (Name varchar(50), Quantity int, [Date] date);
    
    set dateformat dmy;
    insert into T2 values
     ('RM1',100,'20-06-2013'),
     ('RM2',150,'20-06-2013'),
     ('RM3',200,'20-06-2013'),
     ('RM4',140,'20-06-2013'),
     ('RM1',120,'21-06-2013'),
     ('RM2',300,'21-06-2013'),
     ('RM3',120,'21-06-2013'),
     ('RM4',180,'21-06-2013'),
     ('RM1',170,'22-06-2013'),
     ('RM2',100,'22-06-2013'),
     ('RM1',330,'23-06-2013'),
     ('RM2',280,'23-06-2013'); 
    --
    declare @ListaCol varchar(500);
    set @ListaCol='';
    select @ListaCol += '['+convert(varchar, [Date],105)+'],'
      from (select distinct [Date] from T2 order by [Date]) as T;
    set @ListaCol=left(@ListaCol, datalength(@ListaCol)-1);
    print @ListaCol;
    
    --
    declare @ComandoSQL varchar(1500);
    set @ComandoSQL = 'select Name,'+@ListaCol + ' from T2 pivot (sum(Quantity) ' +
             'for [Date] in ('+@ListaCol+')) as P;';
    print @ComandoSQL;     
    --execute (@ComandoSQL);

    José Diz.


    Belo Horizonte, MG - Brasil
    Mark as answered if this post solved your problem and Vote as helpful if this post was useful.

    Monday, June 24, 2013 12:20 AM

All replies

  • From what environment do you run your stored procedure? It may be better to do the pivoting client-side, because it is not easily done in SQL. Since a result set has a fixed structure, you need to use dynamic SQL, and dynamic SQL is advanced feature which is not for newcomers.

    If you are using Reporting Services, I am told that there is a tablix with dynamic pivot. (I don' t know Reporting Services myself.)

    If you insist on doing it in SQL Server, look at this article on my web site: http://www.sommarskog.se/dynamic_sql.html. The section Dynamic Crosstab covers your specific problem, but if you have not worked with dynamic SQL before, you need to learn basics first.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, June 23, 2013 8:31 AM
  • Sunday, June 23, 2013 10:56 PM
  • Just follow the example here:

    http://www.sqlusa.com/bestpractices2005/dynamicpivot/

    You can also apply SSRS Report Wizard to create PIVOT report.


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    Sunday, June 23, 2013 11:52 PM
  • If there was only one date value, and it was known, this would be a way:

    use tempdb;
    create table T1 (Name varchar(50), Quantity int, [Date] date);
    
    set dateformat dmy;
    insert into T1 values
     ('RM1',100,'20-06-2013'),
     ('RM2',150,'20-06-2013'),
     ('RM3',200,'20-06-2013'),
     ('RM4',140,'20-06-2013');
    
    select Name, [20-06-2013]
      from T1
      pivot (sum(Quantity) 
             for [Date] in ([20-06-2013])) as P;


    But, considering that the date values ​​are dynamic:

    create table T2 (Name varchar(50), Quantity int, [Date] date);
    
    set dateformat dmy;
    insert into T2 values
     ('RM1',100,'20-06-2013'),
     ('RM2',150,'20-06-2013'),
     ('RM3',200,'20-06-2013'),
     ('RM4',140,'20-06-2013'),
     ('RM1',120,'21-06-2013'),
     ('RM2',300,'21-06-2013'),
     ('RM3',120,'21-06-2013'),
     ('RM4',180,'21-06-2013'),
     ('RM1',170,'22-06-2013'),
     ('RM2',100,'22-06-2013'),
     ('RM1',330,'23-06-2013'),
     ('RM2',280,'23-06-2013'); 
    --
    declare @ListaCol varchar(500);
    set @ListaCol='';
    select @ListaCol += '['+convert(varchar, [Date],105)+'],'
      from (select distinct [Date] from T2 order by [Date]) as T;
    set @ListaCol=left(@ListaCol, datalength(@ListaCol)-1);
    print @ListaCol;
    
    --
    declare @ComandoSQL varchar(1500);
    set @ComandoSQL = 'select Name,'+@ListaCol + ' from T2 pivot (sum(Quantity) ' +
             'for [Date] in ('+@ListaCol+')) as P;';
    print @ComandoSQL;     
    --execute (@ComandoSQL);

    José Diz.


    Belo Horizonte, MG - Brasil
    Mark as answered if this post solved your problem and Vote as helpful if this post was useful.

    Monday, June 24, 2013 12:20 AM
  • Declare @dynamictable varchar(MAX)
    if object_id('tempdb..#temp') is not null drop table #temp
    if object_id('tempdb..##temp1') is not null drop table ##temp1
    Create Table #temp
    (
    Name varchar(50),
    Quantity int,
    Date1 varchar(50)
    )

    Insert into #temp values
    ('RM1',100,'20-06-2013'),
    ('RM2',150,'20-06-2013'),
    ('RM3',200,'20-06-2013'),
    ('RM4',140,'20-06-2013'),
    ('RM1',100,'21-06-2013'),
    ('RM2',150,'21-06-2013'),
    ('RM3',200,'21-06-2013'),
    ('RM4',140,'21-06-2013'),
    ('RM1',100,'22-06-2013'),
    ('RM2',150,'22-06-2013'),
    ('RM3',200,'22-06-2013'),
    ('RM4',140,'22-06-2013')

    --Select * from #temp
    Declare @Namecount int
    Declare @datecount int
    Set @Namecount = (Select count (*) from (Select distinct name from #temp)as a )
    Set @datecount = (Select count (*) from (Select date1 from #temp)as b )
    Set @dynamictable = 'create table ##temp1
    (
    Name varchar(50),'
    Declare @date1 varchar(50)
    Declare  C1 Cursor for select distinct date1 from #temp
    Open C1
    Fetch next from C1 inTo @date1
    While (@@FETCH_STATUS = 0)
    Begin

    Set @dynamictable =  @dynamictable + '['+@date1 +']' + ' varchar(50),'
    Fetch next from C1 inTo @date1
    End

    --Print @dynamictable
    Declare @len int = (Select LEN(@dynamictable))
    Set @dynamictable = SUBSTRING(@dynamictable,1,@len-1)
    Set @dynamictable = @dynamictable + ' )'
    Close C1
    Deallocate C1

    --Print @dynamictable
    EXEC (@dynamictable);
    --Select * from ##temp1
    ------Dynamic table has been created Now Start inserting datain this table
    Insert into ##temp1(name)
    Select distinct name From #temp
    Declare @name varchar(25),@updatesql varchar(max)
    Declare  C2 Cursor for select distinct name from #temp
    Open C2
    Fetch next from C2 inTo @name
    While (@@FETCH_STATUS = 0)
    Begin
     Declare C3 Cursor for select distinct date1 from #temp
     Open C3
     Fetch next from C3 inTo @date1
     While (@@FETCH_STATUS = 0)
     Begin
     Print @date1
     Set @updatesql = 'Update ##temp1 set [' + @date1 + '] = (Select Quantity from #temp where Name = ' + '''' + @name + '''' + '  and Date1 = ' + ''''+ @date1 + '''' + ')
     where name = ' + ''''+ @name + ''''
     Print @updatesql
     EXEC (@updatesql);
     Fetch next from C3 inTo @date1
     End
     Close C3
     Deallocate C3
    Fetch next from C2 inTo @name
    End
    Close C2
    Deallocate C2

    Select * from ##temp1

    --Hope above code is as per your requirement..

    --Mark as answered if this post solved your problem and Vote as helpful if this post was useful.



    • Edited by Pradeep_DBA Monday, June 24, 2013 1:26 PM To helps other
    Monday, June 24, 2013 12:57 PM