Creating hierarchical formatted output from a table

Question

I have an SQL table like this:

CC Descr C_NO Vol Wt 2050 Des1 123 20 40 2060 Des2 123 30 50 2050 Des1 125 20 40 2060 Des2 125 30 50 2050 Des1 126 20 40 

and I want the result to be as follows:

 2050 Des1 123 20 40 125 20 40 126 20 40 2060 Des2 123 30 50 125 30 50 

How can I do this with TSQL code?

For each similar CC value, which always has the same Descr value, it displays all the C_No, Vol, and Wt values ​​related to that particular CC value in the sequence recorded in the output section.

Answer 1

try it

  DECLARE @t TABLE ( CC BIGINT, Descr NVARCHAR(10), C_NO INT, Vol SMALLINT, WT SMALLINT ) INSERT INTO @t(CC,Descr,C_NO,Vol,WT) VALUES (2050,'Des1',123,20,40) ,(2060,'Des2',123,30,50) ,(2050,'Des1',125,20,40) ,(2060,'Des2',125,30,50) ,(2050,'Des1',126,20,40) ;WITH CTE1 AS( SELECT t1.CC ,t1.Descr ,MergedColumn = STUFF( (SELECT ',' + CAST(C_NO AS VARCHAR(10)) + '/' + CAST(Vol AS VARCHAR(10)) + '/' + CAST(Wt AS VARCHAR(10)) FROM @t AS t2 WHERE t2.CC=t1.CC AND t2.Descr=t2.Descr FOR XML PATH('')),1,1,'') FROM @t t1 GROUP BY t1.CC,t1.Descr) ,CTE2 AS ( SELECT X.CC ,X.Descr ,Y.SplitDataByComma FROM ( SELECT *, CAST('<X>'+REPLACE(F.MergedColumn,',','</X><X>')+'</X>' AS XML) AS xmlfilter FROM CTE1 F )X CROSS APPLY ( SELECT fdata.D.value('.','varchar(50)') AS SplitDataByComma FROM X.xmlfilter.nodes('X') AS fdata(D) )Y ) ,CTE3 AS ( SELECT X.CC ,X.Descr ,Y.SplitDataBySlash , X.SplitDataByComma AS GrpID ,ROW_NUMBER() OVER( PARTITION BY X.SplitDataByComma ORDER BY X.CC,X.Descr ) AS Rn ,X.SplitDataByComma + CAST(CC AS Varchar(200)) + CAST(Descr AS Varchar(200)) CC_Descr FROM ( SELECT *, CAST('<X>'+REPLACE(F.SplitDataByComma,'/','</X><X>')+'</X>' AS XML) AS xmlfilter FROM CTE2 F )X CROSS APPLY ( SELECT fdata.D.value('.','varchar(50)') AS SplitDataBySlash FROM X.xmlfilter.nodes('X') AS fdata(D) )Y ) ,CTE4 AS ( SELECT Rn = ROW_NUMBER() OVER(PARTITION BY CC ORDER BY CC) ,CC ,Descr ,CASE WHEN Rn = 1 THEN CAST (SplitDataBySlash AS VARCHAR(10)) ELSE ' ' END C_NO ,CASE WHEN Rn = 1 THEN ' ' ELSE CAST (SplitDataBySlash AS VARCHAR(10)) END Vol_Wt ,GrpID FROM Cte3 ) ,CTE5 AS( SELECT CC = CASE WHEN Rn > 1 THEN ' ' ELSE CAST(CC AS Varchar(200)) END ,Descr = CASE WHEN Rn > 1 THEN ' ' ELSE CAST(Descr AS Varchar(200)) END ,C_NO ,Vol_Wt ,GrpID FROM Cte4) SELECT CHAR(10) + REPLICATE(SPACE(1),10) + CAST(CC as VARCHAR(100)) + CHAR(10) + REPLICATE(SPACE(1),15) + Descr + CHAR(10) + REPLICATE(SPACE(1),10) + C_NO + CHAR(10) + REPLICATE(SPACE(1),15) + Vol_Wt FROM CTE5 

In text mode (CTRL + T), the result

  2050 Des1 123 20 40 125 20 40 126 20 40 2060 Des2 123 30 50 125 30 50 

And in grid mode (CTRL + D) the result

 (No column name) 2050 Des1 123 20 40 125 20 40 126 20 40 2060 Des2 123 30 50 125 30 50 

However, SQL Server (or any database) is not a place to format, as others have said. Please study this question.

Answer 2

I would not do that. TSQL is used to retrieve data, then you format this data using application code.
But if you are working in the query analyzer and want the output to be there, you can try the following:

 DECLARE mytable_cursor CURSOR FOR SELECT CC, Descr, C_NO, Vol, Wt FROM myTable ORDER BY CC, Descr, C_NO OPEN mytable_cursor; FETCH NEXT FROM mytable_cursor INTO @CC, @Descr, @C_NO, @Vol, @Wt SET @oCC = @CC SET @oDescr = @Descr SET @oC_NO = @C_NO WHILE @@FETCH_STATUS = 0 BEGIN PRINT @CC; WHILE @@FETCH_STATUS = 0 AND @oCC = @CC BEGIN PRINT ' ' + @Descr; WHILE @@FETCH_STATUS = 0 AND @oDescr = @Descr BEGIN PRINT ' ' + @C_NO; WHILE @@FETCH_STATUS = 0 AND @oC_NO = @C_NO BEGIN PRINT ' ' + @Vol; PRINT ' ' + @WT; FETCH NEXT FROM mytable_cursor INTO @CC, @Descr, @C_NO, @Vol, @Wt END SET @oC_NO = @C_NO END SET @oDescr = @Descr END SET @oCC = @CC END CLOSE mytable_cursor; DEALLOCATE mytable_cursor; 

Or you can use GROUP BY ... WITH ROLLUP and CASE WHEN to get the results of a single query.

Here's an example with a dash instead of spaces to make sure formatting is visible:

 SELECT (CASE WHEN Descr IS NULL THEN CC WHEN C_NO IS NULL THEN '----' + Descr WHEN Vol IS NULL THEN '--' + C_NO WHEN Wt IS NULL THEN '------' + Vol ELSE '------' + Wt END) FROM (SELECT CC, Descr, C_NO, Vol, Wt FROM my GROUP BY CC, Descr, C_NO, Vol, Wt WITH ROLLUP) AS x WHERE CC IS NOT NULL ORDER BY CC, Descr, C_NO, Vol, Wt 

Answer 3

You cannot do this in tsql code. You need to do this in the application code.

Answer 4

One of the best ways to present data in a hierarchical order is to use XML. Something else, often the use of XML is associated with high performance.

Using your sample information and this world of code:

 ;WITH DistinctValues (CC,Descr ) AS ( SELECT DISTINCT CC,Descr FROM @TableOne ) SELECT ( SELECT CC AS "CC/@CC" ,Descr AS "CC/Descr" ,( SELECT C_NO AS "C_NO/@C_NO", Vol AS "C_NO/Vol", Wt AS "C_NO/Wt" FROM @TableOne AS Data WHERE Data.CC=DV.CC AND Data.Descr=DV.Descr FOR XML PATH(''),TYPE ) AS "CC" FROM DistinctValues AS DV FOR XML PATH(''),TYPE ) FOR XML PATH('Source'),TYPE 

I get the following result (as a return of one line above):

 <Source> <CC CC="2050"> <Descr>Des1</Descr> <C_NO C_NO="123"> <Vol>20</Vol> <Wt>40</Wt> </C_NO> <C_NO C_NO="125"> <Vol>20</Vol> <Wt>40</Wt> </C_NO> <C_NO C_NO="126"> <Vol>20</Vol> <Wt>40</Wt> </C_NO> </CC> <CC CC="2060"> <Descr>Des2</Descr> <C_NO C_NO="123"> <Vol>30</Vol> <Wt>50</Wt> </C_NO> <C_NO C_NO="125"> <Vol>30</Vol> <Wt>50</Wt> </C_NO> </CC> </Source> 

Note that the XML functions in T-SQL, combined with the structure of the XML language, give you a huge number of possible output formats - you use one or more nodes, you can represent the data as attributes or node text, merge it as you like, and , in your opinion, it will be easier to work with your application level.

Here is all the code (just copy and paste) that I used to create the previous XML structure:

 DECLARE @TableOne TABLE ( CC BIGINT, Descr NVARCHAR(10), C_NO INT, Vol SMALLINT, WT SMALLINT ) INSERT INTO @TableOne(CC,Descr,C_NO,Vol,WT) VALUES (2050,'Des1',123,20,40) ,(2060,'Des2',123,30,50) ,(2050,'Des1',125,20,40) ,(2060,'Des2',125,30,50) ,(2050,'Des1',126,20,40) ;WITH DistinctValues (CC,Descr ) AS ( SELECT DISTINCT CC,Descr FROM @TableOne ) SELECT ( SELECT CC AS "CC/@CC" ,Descr AS "CC/Descr" ,( SELECT C_NO AS "C_NO/@C_NO", Vol AS "C_NO/Vol", Wt AS "C_NO/Wt" FROM @TableOne AS Data WHERE Data.CC=DV.CC AND Data.Descr=DV.Descr FOR XML PATH(''),TYPE ) AS "CC" FROM DistinctValues AS DV FOR XML PATH(''),TYPE ) FOR XML PATH('Source'),TYPE 

It works like a charm and tested on Microsoft SQL Server Management Studio 2012.