Create hierarchical formatted output from table

Question

I have a 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 output like this:

How can I do that using TSQL code?

For every similar CC value which always have similar Descr value, it shows all the C_No, Vol and Wt values related to that particular CC value in the sequence written in the output section.

Your question is well worded, but I'd suggest that you consider doing this in a difference language. TSQL is not known for it's formatting. — BitwiseMan, Sep 07 '12 at 16:58

Niladri Biswas · Answer 1 · 2012-09-10T10:34:09.287

Try this

 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 is

And in Grid Mode(CTRL + D) , the result is

(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 the place to do formatting as others spoke about.Please look into the matter.

BitwiseMan · Answer 2 · 2012-09-07T21:23:04.607

I wouldn't do this. TSQL is used to get the data, then you format that data with application code.
But if you're working in Query analyzer and just want the output 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 a GROUP BY ... WITH ROLLUP and CASE WHEN to get the results a single query.

Here's an example with dashes instead of spaces to make sure the 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

Perhaps you could answer that question of why you're doing this in TSQL? — BitwiseMan, Sep 07 '12 at 20:50

score 0 · Answer 3 · answered Sep 07 '12 at 17:09

0

You can't do this in tsql code. You need to do this sort of thing in the application code.

answered Sep 07 '12 at 17:09

HLGEM

94,695
15
113
186

You _shouldn't_ do this is tsql, but it is possible. – BitwiseMan Jul 25 '14 at 18:50

gotqn · Answer 4 · 2012-09-13T06:11:04.417

One of the best way to represent data in hierarchical order is using XML. Something more, often the use of XML is connected with great performance.

Using your example information and this peace 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 (in the form of one row return by the statement 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 XML language structure gives you a huge of possible variations of output data formats - you use one or many nodes, you can represent the the data as attributes or node text. Combine this as you like and as you feel it will be easiest to work with in your application level.

Here is the whole code (just copy and paste) that I have used to generated 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

This works like a charm and it is tested on Microsoft SQL Server Management Studio 2012.

Create hierarchical formatted output from table

4 Answers4