none
Pivot Query Problem in MS SqlServer 2008

    Question

  • Hi,

    I have a table like below

    ID DocType Number Expiry PopUP

    ================================================

    2083 Visa 02/050051 2013-09-22 10
    2083 Passport AE4855962 2016-02-26 15
    2084 Visa 02/050046 2013-10-26 20
    2084 Passport GG4106392 2015-09-15 30

    I need the result from table like below

    ID VisaNumber VisaExpiry VisaPopUp  PassportNumber PassportExpiry  PassportPopUp

    =====================================================================================

    2083 02/050051 2013-09-22 10 AE4855962  2016-02-26 15

    2084  02/050046 2013-10-26 20  GG4106392  2015-09-15 30

    I Need dynamic query because the 'DocType' column will grow dynamically (in future we can add 'DrivLicence' etc.,)

    Thanks and Regards

    Kasim J

    Thursday, September 12, 2013 4:54 AM

Answers

  • Hi Kasim,

    You can refer to the following commands:

    use tempdb;
    go
    
    
    create table Tbl (ID INT,DocType VARCHAR(100),Number VARCHAR(100),Expiry DATE,PopUP int)
    INSERT INTO Tbl VALUES (2083,'VISA','02/050051','2013-09-22',10),
    				    (2083,'Passport','AE4855962','2016-02-26',15),
    				    (2084,'VISA','02/050046','2013-10-26',20),
    				    (2084,'Passport','GG4106392','2015-09-15',30);
    
    INSERT INTO Tbl VALUES 			
    					(2083,'A','02/050051','2013-09-22',10),
    				    (2083,'B','AE4855962','2016-02-26',15),
    				    (2084,'A','02/050046','2013-10-26',20),
    				    (2084,'B','GG4106392','2015-09-15',30);
    
    
    declare @types int;
    select @types= count(distinct DocType)
    from Tbl
    --print(@types)
    
    declare @sql nvarchar(max);
    declare @sql2 nvarchar(max);
    declare @type varchar(max);
    declare @row int;
    set @row = 1;
    
    set @sql = '; with cte as
    (
    select ID,DocType,Number,Expiry,PopUP,ROW_NUMBER() over(partition by ID order by ID,DocType) as RowNum from Tbl
    )'
    
    while(@row<=@types)
    begin
    
    ;with CteType as
    (
    select DocType,ROW_NUMBER() over(order by DocType) as TypeRowNum
    from tbl
    group by DocType
    )
    
    select @type=DocType
    from CteType
    where TypeRowNum=@row
    
    if @row = 1
    begin
    set @sql = @sql+'select '+ 'c1.ID as ID, c1.Number as '+ @type+ 'Number,c1.Expiry as '+@type+'Expiry,c1.PopUP as '+@type+'PopUP'
    set @sql2 = ' from cte c1'
    end
    else
    begin
    	set @sql = @sql+', c'+convert(nvarchar(max),@row)+'.Number as '+ @type+ 'Number,c'+convert(nvarchar(max),@row)+'.Expiry as '+@type+'Expiry,c'+convert(nvarchar(max),@row)+'.PopUP as '+@type+'PopUP'
    	set @sql2 = @sql2+' inner join cte c'+convert(nvarchar(max),@row)+'
    on c1.ID = c'+convert(nvarchar(max),@row)+'.ID
    and c1.RowNum = c'+convert(nvarchar(max),@row)+'.RowNum -'+convert(nvarchar(max),@row)+'+1'
    end
    
    set @row = @row + 1;
    
    end
    set @sql = @sql+@sql2
    print(@sql)
    execute(@sql)
    
    
    drop table Tbl
    

    Allen Li
    TechNet Community Support

    Sunday, September 15, 2013 2:37 AM

All replies

  • Try this:

    DECLARE @Tbl Table (ID INT,DocType VARCHAR(100),Number VARCHAR(100),Expiry DATE,PopUP int)
    INSERT INTO @Tbl VALUES (2083,'VISA','02/050051','2013-09-22',10),
    				    (2083,'Passport','AE4855962','2016-02-26',15),
    				    (2084,'VISA','02/050046','2013-10-26',20),
    				    (2084,'Passport','GG4106392','2015-09-15',30)
    
    SELECT 
        ID
        ,MAX(CASE DocType WHEN 'VISA' THEN Number END) AS VISANumber
        ,MAX(CASE DocType WHEN 'VISA' THEN Expiry END) AS VISAExpiry
        ,MAX(CASE DocType WHEN 'VISA' THEN PopUP END) AS VISAPopUP
        ,MAX(CASE DocType WHEN 'Passport' THEN Number END) AS PassportNumber
        ,MAX(CASE DocType WHEN 'Passport' THEN Expiry END) AS PassportExpiry
        ,MAX(CASE DocType WHEN 'Passport' THEN PopUP END) AS PassportPopUP
    from @Tbl t
    GROUP BY ID



    Regards Harsh

    • Proposed as answer by Sarat Babu (SS) Thursday, September 12, 2013 6:13 AM
    Thursday, September 12, 2013 5:14 AM
  • Hi harse,

    thanks for your reply.

    In the above query you have hard coded the DocType. but in my case it will vary, may be in future we can add one more Doctype or we can remove it. at that time it will cause an issue (we dont want to change the query at that time). for your reference we have the document type details in separate master table as well.

    Thanks

    Kasim J

    Thursday, September 12, 2013 7:05 AM
  • In that case you may Dynamic Pivot, go through https://www.simple-talk.com/blogs/2007/09/14/pivots-with-dynamic-columns-in-sql-server-2005/ for simple understanding.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, September 12, 2013 7:19 AM
  • Hi,

    Even if i make it dynamic pivot, i can able to make pivot single column only.

    Thanks 

    Kasim J

    Thursday, September 12, 2013 8:46 AM
  • Thangam, I think you'll find that this is a great resource.

    http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/dynamic-pivot-on-multiple-columns

    This is great too.

    https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Saturday, September 14, 2013 5:15 PM
  • Hi Kasim,

    You can refer to the following commands:

    use tempdb;
    go
    
    
    create table Tbl (ID INT,DocType VARCHAR(100),Number VARCHAR(100),Expiry DATE,PopUP int)
    INSERT INTO Tbl VALUES (2083,'VISA','02/050051','2013-09-22',10),
    				    (2083,'Passport','AE4855962','2016-02-26',15),
    				    (2084,'VISA','02/050046','2013-10-26',20),
    				    (2084,'Passport','GG4106392','2015-09-15',30);
    
    INSERT INTO Tbl VALUES 			
    					(2083,'A','02/050051','2013-09-22',10),
    				    (2083,'B','AE4855962','2016-02-26',15),
    				    (2084,'A','02/050046','2013-10-26',20),
    				    (2084,'B','GG4106392','2015-09-15',30);
    
    
    declare @types int;
    select @types= count(distinct DocType)
    from Tbl
    --print(@types)
    
    declare @sql nvarchar(max);
    declare @sql2 nvarchar(max);
    declare @type varchar(max);
    declare @row int;
    set @row = 1;
    
    set @sql = '; with cte as
    (
    select ID,DocType,Number,Expiry,PopUP,ROW_NUMBER() over(partition by ID order by ID,DocType) as RowNum from Tbl
    )'
    
    while(@row<=@types)
    begin
    
    ;with CteType as
    (
    select DocType,ROW_NUMBER() over(order by DocType) as TypeRowNum
    from tbl
    group by DocType
    )
    
    select @type=DocType
    from CteType
    where TypeRowNum=@row
    
    if @row = 1
    begin
    set @sql = @sql+'select '+ 'c1.ID as ID, c1.Number as '+ @type+ 'Number,c1.Expiry as '+@type+'Expiry,c1.PopUP as '+@type+'PopUP'
    set @sql2 = ' from cte c1'
    end
    else
    begin
    	set @sql = @sql+', c'+convert(nvarchar(max),@row)+'.Number as '+ @type+ 'Number,c'+convert(nvarchar(max),@row)+'.Expiry as '+@type+'Expiry,c'+convert(nvarchar(max),@row)+'.PopUP as '+@type+'PopUP'
    	set @sql2 = @sql2+' inner join cte c'+convert(nvarchar(max),@row)+'
    on c1.ID = c'+convert(nvarchar(max),@row)+'.ID
    and c1.RowNum = c'+convert(nvarchar(max),@row)+'.RowNum -'+convert(nvarchar(max),@row)+'+1'
    end
    
    set @row = @row + 1;
    
    end
    set @sql = @sql+@sql2
    print(@sql)
    execute(@sql)
    
    
    drop table Tbl
    

    Allen Li
    TechNet Community Support

    Sunday, September 15, 2013 2:37 AM
  • In addition to this blog I also published this article on the same topic that should help as well

    http://social.technet.microsoft.com/wiki/contents/articles/17510.t-sql-dynamic-pivot-on-multiple-columns.aspx


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Sunday, September 15, 2013 3:28 AM