want final output as for all distinct Apkkey, all columns from first row and take only data columns from next row and append those columns to upper string.

Akshay Chavan 1 Reputation point
2022-09-06T06:53:21.303+00:00

I am working one query and after performing multiple operations I got the below output in temp table. I have created one sample table here as original table have over 50 columns.

238027-image.png

I want final output as for all distinct Apkkey, all columns from first row and take only data columns from next row and append those columns to upper string.
eg
abc111 a1 a1 a1 111 a1 a1 a1 a1 a1 10 11 12 13 14 20 21 22 23 24
abc111 a1 a1 a1 222 a1 a1 a1 a1 a1 10 11 12 13 14 20 21 22 23 24 30 31 32 33 34
def111 b1 b1 b1 333 b1 b1 b1 b1 b1 10 11 12 13 14 20 21 22 23 24

I tried by using cursor also the while loops but not getting the expected output.
Can someone help me here. I am little new in sql server.

Attached script for creating test tables so that you can test the solution.

thank you so much for your help in advance!

Script:

create table TestTable(RowNm int, FolioKey varchar(10),col1 varchar(10),col2 varchar(10),col3 varchar(10),ApkKey varchar(10),col5 varchar(10),
col6 varchar(10),col7 varchar(10),col8 varchar(10),col9 varchar(10),data1 varchar(10),data2 varchar(10),data3 varchar(10),data4 varchar(10),data5 varchar(10))

insert into TestTable values(1,'abc111','a1','a1','a1','111','a1','a1','a1','a1','a1','10','11','12','13','14')
insert into TestTable values(2,'abc111','a1','a1','a1','111','a1','a1','a1','a1','a1','20','21','22','23','24')
insert into TestTable values(1,'abc111','a1','a1','a1','222','a1','a1','a1','a1','a1','10','11','12','13','14')
insert into TestTable values(2,'abc111','a1','a1','a1','222','a1','a1','a1','a1','a1','20','21','22','23','24')
insert into TestTable values(3,'abc111','a1','a1','a1','222','a1','a1','a1','a1','a1','30','31','32','33','34')
insert into TestTable values(1,'def111','b1','b1','b1','333','b1','b1','b1','b1','b1','10','11','12','13','14')
insert into TestTable values(2,'def111','b1','b1','b1','333','b1','b1','b1','b1','b1','20','21','22','23','24')
insert into TestTable values(1,'def111','b1','b1','b1','444','b1','b1','b1','b1','b1','10','11','12','13','14')
insert into TestTable values(2,'def111','b1','b1','b1','444','b1','b1','b1','b1','b1','20','21','22','23','24')
insert into TestTable values(3,'def111','b1','b1','b1','444','b1','b1','b1','b1','b1','30','31','32','33','34')
insert into TestTable values(4,'def111','b1','b1','b1','444','b1','b1','b1','b1','b1','20','21','22','23','24')
insert into TestTable values(5,'def111','b1','b1','b1','444','b1','b1','b1','b1','b1','30','31','32','33','34')

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,157 questions
{count} votes

4 answers

Sort by: Most helpful
  1. CosmogHong-MSFT 9,161 Reputation points Microsoft Vendor
    2022-09-06T08:43:45.827+00:00

    Hi @Akshay Chavan
    Check this:

    DECLARE @Count INT =1  
    DECLARE @Sql_String VARCHAR(MAX) =  
    ';WITH CTE1 AS  
    (SELECT FolioKey,col1,col2,col3,ApkKey,col5,col6,col7,col8,col9  
           ,data'+CAST((@Count-1)*5+1 AS VARCHAR(20))+'  
    	   ,data'+CAST((@Count-1)*5+2 AS VARCHAR(20))+'  
    	   ,data'+CAST((@Count-1)*5+3 AS VARCHAR(20))+'  
    	   ,data'+CAST((@Count-1)*5+4 AS VARCHAR(20))+'  
    	   ,data'+CAST((@Count-1)*5+5 AS VARCHAR(20))+'   
     FROM TestTable WHERE RowNm=1)'  
    --PRINT @Sql_String  
      
    WHILE (SELECT COUNT(*) FROM TestTable WHERE RowNm = @Count + 1) > 0  
    BEGIN   
      SET @Sql_String=@Sql_String+  
      ',CTE'+CAST(@Count+1 AS VARCHAR(20))+'   
       AS(SELECT C.*,T.data1 AS data'+CAST((@Count)*5+1 AS VARCHAR(20))+'  
                    ,T.data2 AS data'+CAST((@Count)*5+2 AS VARCHAR(20))+'  
    				,T.data3 AS data'+CAST((@Count)*5+3 AS VARCHAR(20))+'  
    				,T.data4 AS data'+CAST((@Count)*5+4 AS VARCHAR(20))+'  
    				,T.data5 AS data'+CAST((@Count)*5+5 AS VARCHAR(20))+'  
          FROM CTE'+CAST(@Count AS VARCHAR(20))+' C LEFT JOIN TestTable T ON C.ApkKey=T.ApkKey AND T.RowNm='+CAST(@Count+1 AS VARCHAR(20))+')'  
      SET @Count= @Count + 1  
    END  
    SET @Sql_String=@Sql_String+'SELECT * FROM CTE'+CAST(@Count AS VARCHAR(20))+''  
    PRINT @Sql_String  
    EXEC (@Sql_String)  
    

    Best regards,
    LiHong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
    No comments

  2. Akshay Chavan 1 Reputation point
    2022-09-06T09:00:59.4+00:00

    Hello @CosmogHong-MSFT

    Thank you for your response. But this is not what I am looking for. may be as you are running over max rownm it is giving NULL for some rows.
    238068-image.png

    Actually, I was looking for resultset as

    abc111 a1 a1 a1 111 a1 a1 a1 a1 a1 10 11 12 13 14 20 21 22 23 24
    abc111 a1 a1 a1 222 a1 a1 a1 a1 a1 10 11 12 13 14 20 21 22 23 24 30 31 32 33 34
    def111 b1 b1 b1 333 b1 b1 b1 b1 b1 10 11 12 13 14 20 21 22 23 24

    can I ask for your help here?

    Thanks,
    Akshay

  3. CosmogHong-MSFT 9,161 Reputation points Microsoft Vendor
    2022-09-06T09:34:20.343+00:00

    Hi @Akshay Chavan
    You could use IFNULL function to change NULL value to blank, like this:

    DECLARE @Count INT =1  
    DECLARE @Sql_String VARCHAR(MAX) =  
    ';WITH CTE1 AS  
    (SELECT FolioKey,col1,col2,col3,ApkKey,col5,col6,col7,col8,col9  
           ,data'+CAST((@Count-1)*5+1 AS VARCHAR(20))+'  
    	   ,data'+CAST((@Count-1)*5+2 AS VARCHAR(20))+'  
    	   ,data'+CAST((@Count-1)*5+3 AS VARCHAR(20))+'  
    	   ,data'+CAST((@Count-1)*5+4 AS VARCHAR(20))+'  
    	   ,data'+CAST((@Count-1)*5+5 AS VARCHAR(20))+'   
     FROM TestTable WHERE RowNm=1)'  
    --PRINT @Sql_String  
      
    WHILE (SELECT COUNT(*) FROM TestTable WHERE RowNm = @Count + 1) > 0  
    BEGIN   
      SET @Sql_String=@Sql_String+  
      ',CTE'+CAST(@Count+1 AS VARCHAR(20))+'   
       AS(SELECT C.*,ISNULL(T.data1,'''') AS data'+CAST((@Count)*5+1 AS VARCHAR(20))+'  
                    ,ISNULL(T.data2,'''') AS data'+CAST((@Count)*5+2 AS VARCHAR(20))+'  
    				,ISNULL(T.data3,'''') AS data'+CAST((@Count)*5+3 AS VARCHAR(20))+'  
    				,ISNULL(T.data4,'''') AS data'+CAST((@Count)*5+4 AS VARCHAR(20))+'  
    				,ISNULL(T.data5,'''') AS data'+CAST((@Count)*5+5 AS VARCHAR(20))+'  
          FROM CTE'+CAST(@Count AS VARCHAR(20))+' C LEFT JOIN TestTable T ON C.ApkKey=T.ApkKey AND T.RowNm='+CAST(@Count+1 AS VARCHAR(20))+')'  
      SET @Count= @Count + 1  
    END  
    SET @Sql_String=@Sql_String+'SELECT * FROM CTE'+CAST(@Count AS VARCHAR(20))+''  
    --PRINT @Sql_String  
    EXEC (@Sql_String)
    
  4. Akshay Chavan 1 Reputation point
    2022-09-07T00:29:59.037+00:00

    Thank you so much @CosmogHong-MSFT for your efforts.

    One last help, can you tell me how to store result of this EXEC (@alenzi _String) from your query into string like I mentioned earlier?

    abc111 a1 a1 a1 111 a1 a1 a1 a1 a1 10 11 12 13 14 20 21 22 23 24
    abc111 a1 a1 a1 222 a1 a1 a1 a1 a1 10 11 12 13 14 20 21 22 23 24 30 31 32 33 34
    def111 b1 b1 b1 333 b1 b1 b1 b1 b1 10 11 12 13 14 20 21 22 23 24

    Regards,
    Akshay