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.
12,695 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. CosmogHong-MSFT 22,861 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.
    0 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

    0 comments
  3. CosmogHong-MSFT 22,861 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)
    0 comments
  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