none
How to take sql query results and turn vertical and output in specific format RRS feed

  • Question

  • Really new to SQL, hoping someone can help me. Have this query

    SELECT
         1
        ,2
        ,20
        ,50
        ,100
        ,200
        ,201
        ,202
        ,203
        ,204
        ,205
        ,206
        ,210
        ,215
        ,220
        ,221
        ,225
        ,230
        ,235
        ,240
        ,245
        ,250
        ,255
        ,261
        ,262
        ,263
        ,368
        ,369
    FROM Data_Table 
    ORDER BY CAST(3 AS DATE), 6,  1 DESC

    That outputs the data like this

    1      2            20           50    100       200   201   202   203  204  205  206  210   215 ....369
    1000A  Test Desc 1  02-01-1993   Yes   Testing   AAA                                   1.00 
    1000A  Test Desc 2  09-01-1999   Yes   Testing   BBB                                   2.00
                        09-01-1999                   CCC                         CC        3.00
    1000A  Test Desc 3  02-01-2006   Yes   Testing   DDD                                   4.00
    1000A  Test Desc 4  01-01-2008   Yes   Testing   EEE                         EE        5.00
                        01-01-2008                   FFF                                   6.00

    I need to have it output the data vertically in the format below. When columns 1, 2, 50 & 100 have data in them, then it needs to include it in the output file as shown, but when there is no data in columns 1, 2, 50 & 100 then dont output those rows.

    ##Header1
    ##Header2
    1,1000A
    2,Test Desc 1
    20,02-01-1993
    50,Yes
    100,Testing
    200,AAA
    201,
    202,
    203,
    204,
    205,
    206,
    210,1
    215,
    220,
    221,
    225,
    230,
    235,
    240,
    245,
    250,
    255,
    261,
    262,
    263,
    368,
    369,
    1,1000A
    2,Test Desc 2
    20,04-01-1999
    50,Yes
    100,Testing
    200,BBB
    201,
    202,
    203,
    204,
    205,
    206,
    210,2
    215,
    220,
    221,
    225,
    230,
    235,
    240,
    245,
    250,
    255,
    261,
    262,
    263,
    368,
    369,
    20,04-01-1999
    200,CCC
    201,
    202,
    203,
    204,
    205,CC
    206,
    210,3
    215,
    220,
    221,
    225,
    230,
    235,
    240,
    245,
    250,
    255,
    261,
    262,
    263,
    368,
    369,
    1,1000A
    2,Test Desc 3
    20,02-01-2006
    50,Yes
    100,Testing
    200,DDD
    201,
    202,
    203,
    204,
    205,
    206,
    210,4
    215,
    220,
    221,
    225,
    230,
    235,
    240,
    245,
    250,
    255,
    261,
    262,
    263,
    368,
    369,
    1,1000A
    2,Test Desc 4
    20,01-01-2008
    50,Yes
    100,Testing
    200,EEE
    201,
    202,
    203,
    204,
    205,EE
    206,
    210,5
    215,
    220,
    221,
    225,
    230,
    235,
    240,
    245,
    250,
    255,
    261,
    262,
    263,
    368,
    369,
    20,01-01-2008
    200,FFF
    201,
    202,
    203,
    204,
    205,
    206,
    210,6
    215,
    220,
    221,
    225,
    230,
    235,
    240,
    245,
    250,
    255,
    261,
    262,
    263,
    368,
    369,


    Friday, January 10, 2020 9:43 PM

All replies

  • This is difficult to understand. The query you posted will post the numbers in the SELECT list as many times there are rows in Data_Table, so it is not a particularly meaningful query.

    For help with this type of queries, it is often a good idea to post the CREATE TABLE statement for your table, and INSERT statements with sample data. We also need to know what the result you are looking for. The result you posted is difficult to understand, because some formatting accident has made into a single column.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, January 11, 2020 11:00 AM
  • It looks like you need dynamic pivot sql, as Erland pointed it would be better of providing sample data along with desired result with readable format 

    CREATE TABLE Foo (
      foo_type INT  PRIMARY KEY,
      foo_value CHAR(1));

    INSERT INTO Foo VALUES(1,'A');
    INSERT INTO Foo VALUES(2,'B');
    INSERT INTO Foo VALUES(3,'C');
    INSERT INTO Foo VALUES(99,'Y');
    INSERT INTO Foo VALUES(100,'U');




    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, January 12, 2020 8:28 AM
    Answerer


  • -- dynamic pivot (SQL Server 2005/2008)
    DECLARE @pivot_cols NVARCHAR(1000);
    DECLARE @pivot_query NVARCHAR(2000);
    SELECT @pivot_cols =
            STUFF((SELECT  '],[' + foo_type 
                   FROM (SELECT  CAST(foo_type AS VARCHAR(10))foo_type,
          ROW_NUMBER ()  OVER (ORDER BY CAST(foo_type  AS INT)) rn 
          FROM Foo ) AS DER
                   ORDER BY '],[' +  CAST(rn  AS VARCHAR(10))
                   FOR XML PATH('')
                   ), 1, 2, '') + ']';
    SELECT @pivot_cols
    SET @pivot_query =
    N'SELECT ' + @pivot_cols +
      'FROM Foo ' +
      'PIVOT ' +
      '(MAX(foo_value) FOR foo_type IN (' + @pivot_cols + ')) AS P;';

    EXEC(@pivot_query);



    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, January 12, 2020 8:28 AM
    Answerer
  • Hi iam2nd2god, 

    Did you mean that if your the whole column value is equal to '' or null, you will not show the whole column in your result? I do a simple example , please check it .

    If I have any incorrect understanding , please let me know.

    IF OBJECT_ID('test') IS NOT NULL drop table  test   
    go 
    create table test (
    a varchar(5), 
    b varchar(5),
    c varchar(5),
    d varchar(5)
    )
    insert into test values 
    ('a',null,'v',''),
    ('',null,'ff',''),
    ('g',null,'v','')
    /*
    a     b     c     d
    ----- ----- ----- -----
    a     NULL  v     
          NULL  ff    
    g     NULL  v        
    */
    go
    
    DECLARE @ColNames1 nvarchar(max);
    DECLARE @ColNames2 nvarchar(max);
    DECLARE @column_value nvarchar(max);
    DECLARE @sql nvarchar(1000);
    SELECT @ColNames1 = ISNULL(@ColNames1 + ',','') + '
    	sum(case when ' + QUOTENAME(COLUMN_NAME)+'='''' or '+ QUOTENAME(COLUMN_NAME)+'is null then 0 else 1 end ) '+QUOTENAME(COLUMN_NAME)
        FROM INFORMATION_SCHEMA.COLUMNS p2
        WHERE TABLE_NAME = 'test'
    SELECT @ColNames2 = ISNULL(@ColNames2 + ',','') + QUOTENAME(COLUMN_NAME)
        FROM INFORMATION_SCHEMA.COLUMNS p2
        WHERE TABLE_NAME = 'test'
    set @sql= N'
    ;with cte as(
    select *
    from (select '+@ColNames1+N'
    from test) t UNPIVOT ([Value] 
    FOR [Column] IN (' + +@ColNames2 + N'))  u )
    SELECT @column_value=stuff((
        SELECT DISTINCT '','' + QUOTENAME([Column])
        from cte
        where Value<>0
        FOR XML PATH(''''), TYPE).value(''.'', ''varchar(max)'')
                ,1,1,'''') '
    print(@sql)
    /*
    ;with cte as(
    select *
    from (select 
    	sum(case when [a]='' or [a]is null then 0 else 1 end ) [a],
    	sum(case when [b]='' or [b]is null then 0 else 1 end ) [b],
    	sum(case when [c]='' or [c]is null then 0 else 1 end ) [c],
    	sum(case when [d]='' or [d]is null then 0 else 1 end ) [d]
    from test) t UNPIVOT ([Value] 
    FOR [Column] IN ([a],[b],[c],[d]))  u )
    SELECT @column_value=stuff((
        SELECT DISTINCT ',' + QUOTENAME([Column])
        from cte
        where Value<>0
        FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
                ,1,1,'') 
    */
    exec sp_executesql @sql,N'@column_value nvarchar(max) output',
                             @column_value = @column_value output
    
    set @sql ='select '+@column_value+'from test '
    print (@sql)
    execute(@sql)
    /*
    select [a],[c]from test 
    a     c
    ----- -----
    a     v
          ff
    g     v
    */
    
    

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 13, 2020 6:43 AM
  •  

    Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 15, 2020 9:21 AM
  • --SQL Server 2016 or above
    CREATE TABLE data_table(
       [1] VARCHAR(6)  
      ,[2] VARCHAR(16) 
      ,[20] DATE   
      ,[50] VARCHAR(4)
      ,[100] VARCHAR(9)
      ,[200] VARCHAR(5)  
      ,[201] VARCHAR(5)
      ,[202] VARCHAR(5)
      ,[203] VARCHAR(5)
      ,[204] VARCHAR(3)
      ,[205] VARCHAR(5)
      ,[206] VARCHAR(5)
      ,[210] NUMERIC(6,2)  
      ,[215] VARCHAR(30)
      ,[369] VARCHAR(30)
    );
    INSERT INTO data_table
    VALUES
     ('1000A','Test Desc 1','02-01-1993','Yes','Testing','AAA',NULL,NULL,NULL,NULL,NULL,NULL,1.00,NULL,NULL)
    ,('1000A','Test Desc 2','09-01-1999','Yes','Testing','BBB',NULL,NULL,NULL,NULL,NULL,NULL,2.00,NULL,NULL)
    ,(NULL,NULL,'09-01-1999',NULL,NULL,'CCC',NULL,NULL,NULL,NULL,'CC',NULL,3,NULL,NULL)
    ,('1000A','Test Desc 3','02-01-2006','Yes','Testing','DDD',NULL,NULL,NULL,NULL,NULL,NULL,4.00,NULL,NULL)
    ,('1000A','Test Desc 4','01-01-2008','Yes','Testing','EEE',NULL,NULL,NULL,NULL,'EE',NULL,5,NULL,NULL)
    ,(NULL,NULL,'01-01-2008',NULL,NULL,'FFF',NULL,NULL,NULL,NULL,NULL,NULL,6.00,NULL,NULL);
    
    
     ;with mycte   (TheKey, TheValue) as (
    SELECT [KEY], Value from OpenJson((
    select * from data_table  FOR JSON AUTO, INCLUDE_NULL_VALUES   ))
    WHERE type = 5)
     
    SELECT
     --src.TheKey +1 as colOrder,  
     unpvt.[Key], unpvt.Value 
     FROM mycte AS src
     Cross APPLY OpenJson(src.TheValue) AS unpvt  
     WHERE unpvt.Type!=5  
    and   Not ([Key] in ('1','2','50','100') and Value is null)
    
    
    
     
       
    
    drop table data_table

    Wednesday, January 15, 2020 8:52 PM
    Moderator