none
Union results and append columns to the end of results RRS feed

  • Question

  • Hi,

    I need help with how to write a sql code to do the following.  Here is a sample set of data:

    DECLARE @TT TABLE (
    NAME varchar(50),
    ABV_NAME varchar(10),
    COURSE_NAME varchar(50),
    LOCATION varchar(50),
    SCORE numeric(10,0)
    );



    INSERT INTO @TT VALUES
    ('BOB SMITH','18-19','ELA','NORTH HIGH','525'),
    ('BOB SMITH','17-18','ELA','SOUTH MIDDLE','528');


    SELECT * FROM @TT 



    I am trying to get a result like this.  Any suggestions of how to do this?  I want it all on one line.  Thanks for the help


    BOB SMITH    18-19    ELA     NORTH HIGH  525     17-18   ELA   SOUTH MIDDLE    528



    • Edited by skiggity100 Thursday, November 7, 2019 10:39 PM
    Thursday, November 7, 2019 10:32 PM

Answers

  • Hi , 

    Please try my script.

    ---------dynamic code
    go
    DECLARE @TT TABLE (
    NAME varchar(50),
    ABV_NAME varchar(10),
    COURSE_NAME varchar(50),
    LOCATION varchar(50),
    SCORE numeric(10,0)
    );
    INSERT INTO @TT VALUES
    ('RICK JONES','18-19','ELA','SOUTH MIDDLE','515'),
    ('RICK JONES','17-18','ELA','SOUTH MIDDLE','520'),
    ('BOB SMITH','18-19','ELA','NORTH HIGH','525'),
    ('BOB SMITH','17-18','ELA','SOUTH MIDDLE','528'),
    ('BOB SMITH','16-17','ELA','SOUTH MIDDLE','529'),
    ('KYLE SMITH','18-19','ELA','SOUTH MIDDLE','515'),
    ('KYLE SMITH','17-18','ELA','SOUTH MIDDLE','520'),
    ('KYLE SMITH','16-17','ELA','SOUTH MIDDLE','521'),
    ('KYLE SMITH','15-16','ELA','SOUTH MIDDLE','523');
    
    DECLARE @sql_str VARCHAR(max)
    DECLARE @sql_col VARCHAR(max)
    DECLARE @sql_colp VARCHAR(max)
    DECLARE @sql_colpp VARCHAR(max)
    DECLARE @sql_colppp VARCHAR(max)
    DECLARE @sql_colstring VARCHAR(max)
    DECLARE @sql VARCHAR(max)
    
    
    DECLARE @max_vaue int=(select max(rn) from (select ROW_NUMBER()OVER(partition by Name Order by COURSE_NAME) as rn  from @TT) t  )
    declare @i int=1
    
    while (@i<=@max_vaue)
    begin
    SELECT @sql_col =ISNULL(@sql_col + ',','')+ QUOTENAME(@i) 
    SELECT @sql_colp =ISNULL(@sql_colp + ',','')+ QUOTENAME('P'+cast (@i as varchar(10))) 
    SELECT @sql_colpp =ISNULL(@sql_colpp + ',','')+ QUOTENAME('PP'+cast (@i as varchar(10))) 
    SELECT @sql_colppp =ISNULL(@sql_colppp + ',','')+ QUOTENAME('PPP'+cast (@i as varchar(10))) 
    SELECT @sql_colstring=ISNULL(@sql_colstring + ',','')+ 'max('+QUOTENAME(cast (@i as varchar(10)))
    +') AS  ABV_NAME,max('+QUOTENAME('P'+cast (@i as varchar(10)))
    +') as COURSE_NAME,max('+QUOTENAME('PP'+cast (@i as varchar(10)))
    +') as LOCATION,max('+QUOTENAME('PPP'+cast (@i as varchar(10)))+') as SCORE'
    set @i=@i+1
    end
    
    set @sql = '
    DECLARE @TT TABLE (
    NAME varchar(50),
    ABV_NAME varchar(10),
    COURSE_NAME varchar(50),
    LOCATION varchar(50),
    SCORE numeric(10,0)
    );
    INSERT INTO @TT VALUES
    (''RICK JONES'',''18-19'',''ELA'',''SOUTH MIDDLE'',''515''),
    (''RICK JONES'',''17-18'',''ELA'',''SOUTH MIDDLE'',''520''),
    (''BOB SMITH'',''18-19'',''ELA'',''NORTH HIGH'',''525''),
    (''BOB SMITH'',''17-18'',''ELA'',''SOUTH MIDDLE'',''528''),
    (''BOB SMITH'',''16-17'',''ELA'',''SOUTH MIDDLE'',''529''),
    (''KYLE SMITH'',''18-19'',''ELA'',''SOUTH MIDDLE'',''515''),
    (''KYLE SMITH'',''17-18'',''ELA'',''SOUTH MIDDLE'',''520''),
    (''KYLE SMITH'',''16-17'',''ELA'',''SOUTH MIDDLE'',''521''),
    (''KYLE SMITH'',''15-16'',''ELA'',''SOUTH MIDDLE'',''523'');
    Select NAME,'+@sql_colstring+'
    from  (
    select *,
    ROW_NUMBER()OVER(partition by Name Order by COURSE_NAME ) P,
    ''P''+CAST(ROW_NUMBER()OVER(partition by Name Order by COURSE_NAME)AS VARCHAR) PP,
    ''PP''+CAST(ROW_NUMBER()OVER(partition by Name Order by COURSE_NAME)AS VARCHAR) PPP,
    ''PPP''+CAST(ROW_NUMBER()OVER(partition by Name Order by COURSE_NAME)AS VARCHAR) PPPP
    from @TT  )T
    PIVOT (MAX(ABV_NAME) FOR P IN  ('+@sql_col+')) AS P
    PIVOT (MAX(COURSE_NAME) FOR PP IN ('+@sql_colp+')) AS P
    PIVOT (MAX(LOCATION) FOR PPP IN ('+@sql_colpp+')) AS P
    PIVOT (MAX(SCORE) FOR PPPP IN ('+@sql_colppp+')) AS P
    group by NAME
    '
    print (@sql)
    exec (@sql)
    /*
    DECLARE @TT TABLE (
    NAME varchar(50),
    ABV_NAME varchar(10),
    COURSE_NAME varchar(50),
    LOCATION varchar(50),
    SCORE numeric(10,0)
    );
    INSERT INTO @TT VALUES
    ('RICK JONES','18-19','ELA','SOUTH MIDDLE','515'),
    ('RICK JONES','17-18','ELA','SOUTH MIDDLE','520'),
    ('BOB SMITH','18-19','ELA','NORTH HIGH','525'),
    ('BOB SMITH','17-18','ELA','SOUTH MIDDLE','528'),
    ('BOB SMITH','16-17','ELA','SOUTH MIDDLE','529'),
    ('KYLE SMITH','18-19','ELA','SOUTH MIDDLE','515'),
    ('KYLE SMITH','17-18','ELA','SOUTH MIDDLE','520'),
    ('KYLE SMITH','16-17','ELA','SOUTH MIDDLE','521'),
    ('KYLE SMITH','15-16','ELA','SOUTH MIDDLE','523');
    Select NAME,max([1]) AS  ABV_NAME,max([P1]) as COURSE_NAME,max([PP1]) as LOCATION,max([PPP1]) as SCORE,max([2]) AS  ABV_NAME,max([P2]) as COURSE_NAME,max([PP2]) as LOCATION,max([PPP2]) as SCORE,max([3]) AS  ABV_NAME,max([P3]) as COURSE_NAME,max([PP3]) as LOCATION,max([PPP3]) as SCORE,max([4]) AS  ABV_NAME,max([P4]) as COURSE_NAME,max([PP4]) as LOCATION,max([PPP4]) as SCORE
    from  (
    select *,
    ROW_NUMBER()OVER(partition by Name Order by COURSE_NAME ) P,
    'P'+CAST(ROW_NUMBER()OVER(partition by Name Order by COURSE_NAME)AS VARCHAR) PP,
    'PP'+CAST(ROW_NUMBER()OVER(partition by Name Order by COURSE_NAME)AS VARCHAR) PPP,
    'PPP'+CAST(ROW_NUMBER()OVER(partition by Name Order by COURSE_NAME)AS VARCHAR) PPPP
    from @TT  )T
    PIVOT (MAX(ABV_NAME) FOR P IN  ([1],[2],[3],[4])) AS P
    PIVOT (MAX(COURSE_NAME) FOR PP IN ([P1],[P2],[P3],[P4])) AS P
    PIVOT (MAX(LOCATION) FOR PPP IN ([PP1],[PP2],[PP3],[PP4])) AS P
    PIVOT (MAX(SCORE) FOR PPPP IN ([PPP1],[PPP2],[PPP3],[PPP4])) AS P
    group by NAME
    
    (9 rows affected)
    NAME                                               ABV_NAME   COURSE_NAME                                        LOCATION                                           SCORE                                   ABV_NAME   COURSE_NAME                                        LOCATION                                           SCORE                                   ABV_NAME   COURSE_NAME                                        LOCATION                                           SCORE                                   ABV_NAME   COURSE_NAME                                        LOCATION                                           SCORE
    -------------------------------------------------- ---------- -------------------------------------------------- -------------------------------------------------- --------------------------------------- ---------- -------------------------------------------------- -------------------------------------------------- --------------------------------------- ---------- -------------------------------------------------- -------------------------------------------------- --------------------------------------- ---------- -------------------------------------------------- -------------------------------------------------- ---------------------------------------
    BOB SMITH                                          18-19      ELA                                                NORTH HIGH                                         525                                     17-18      ELA                                                SOUTH MIDDLE                                       528                                     16-17      ELA                                                SOUTH MIDDLE                                       529                                     NULL       NULL                                               NULL                                               NULL
    KYLE SMITH                                         18-19      ELA                                                SOUTH MIDDLE                                       515                                     17-18      ELA                                                SOUTH MIDDLE                                       520                                     16-17      ELA                                                SOUTH MIDDLE                                       521                                     15-16      ELA                                                SOUTH MIDDLE                                       523
    RICK JONES                                         18-19      ELA                                                SOUTH MIDDLE                                       515                                     17-18      ELA                                                SOUTH MIDDLE                                       520                                     NULL       NULL                                               NULL                                               NULL                                    NULL       NULL                                               NULL                                               NULL
    */

    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.

    • Marked as answer by skiggity100 Monday, November 11, 2019 8:08 PM
    Monday, November 11, 2019 8:30 AM

All replies

  • DECLARE @TT TABLE (
    NAME varchar(50),
    ABV_NAME varchar(10),
    COURSE_NAME varchar(50),
    LOCATION varchar(50),
    SCORE numeric(10,0)
    );
    
    
    
    INSERT INTO @TT VALUES
    ('BOB SMITH','18-19','ELA','NORTH HIGH','525'),
    ('BOB SMITH','17-18','ELA','SOUTH MIDDLE','528');
    
    ;with mycte as (
    SELECT NAME  ,
    ABV_NAME ,
    COURSE_NAME ,
    LOCATION ,
    SCORE , row_number() Over(partition by Name Order by score) rn  FROM @TT
    
    )
    
    select m1.NAME  ,
    m1.ABV_NAME ,
    m1.COURSE_NAME ,
    m1.LOCATION ,
    m1.SCORE, 
    m2.ABV_NAME  ABV_NAME2,
    m2.COURSE_NAME COURSE_NAME2,
    m2.LOCATION LOCATION2,
    m2.SCORE SCORE2
     from mycte m1 join mycte m2 
     on m1.NAME=m2.NAME and m1.rn=m2.rn-1 

    Thursday, November 7, 2019 11:12 PM
    Moderator
  • Thank you for trying to post to DDL. Unfortunately you don’t seem to know the table by definition must have a key. This is not an option. You also don’t seem to know the ISO 11179 rules for naming data elements. I also thought it was very magical that you don’t have any fixed length columns that everything works out to be 50 characters long. I’d love to see the careful planning and research you did on this and that you were just being lazy. Here’s a quick cleanup on what you did post. What you had was not a table at all really a deck of punch cards because there is no way could ever have a key with nulls in all the columns this is a common beginner’s mistake you usually unlearn it in about three weeks.

    There is a reason that books, newspapers and virtually everything else that has to be read by human being is written in lowercase. People who work with Latin, Greek or Cyrillic alphabets have trained themselves over the centuries to see all capitals as a Bouma. The way you wrote your code will make it harder and harder to maintain, adding 8 to 12% to the cost of maintaining it (see research done by DOD for the U.S. Army).

    CREATE TABLE Course_Test_Scores

    (student_name VARCHAR(25) NOT NULL,

    abv_name VARCHAR(10) NOT NULL,

    course_name CHAR(3) NOT NULL,

    course_location VARCHAR(30) NOT NULL, – or student location?

    course_score NUMERIC(5,0) NOT NULL,

    PRIMARY KEY(???));

    INSERT INTO Course_Test_Scores VALUES

    ('Bob Smith', '18-19', 'ELA', 'North High', '525'),

    ('Bob Smith', '17-18', 'ELA', 'South Middle', '528');

    >> I am trying to get a result like this. Any suggestions of how to do this? I want it all on one line. Thanks for the help. <<

    This is another beginner’s mistake people usually stop doing after the third or fourth week in SQL class. You’re trying to use SQL to generate a report. A query is not a report. This sort of formatting is done in the presentation layer, outside the database. Think about it for a minute; what if Bob Smith had taken 42 classes?


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, November 8, 2019 2:38 AM
  • Hi , 

    Thank you foe your issue . If you only have two rows , please try my first code . And if you have many rows and would like to make them in a row , please try the second dynamic code.

    Please check it .

    -------the first one
    DECLARE @TT TABLE (
    NAME varchar(50),
    ABV_NAME varchar(10),
    COURSE_NAME varchar(50),
    LOCATION varchar(50),
    SCORE numeric(10,0)
    );
    INSERT INTO @TT VALUES
    ('BOB SMITH','18-19','ELA','NORTH HIGH','525'),
    ('BOB SMITH','17-18','ELA','SOUTH MIDDLE','528');
    ----------static code
    Select NAME,
    max([1]) as ABV_NAME,max([P1]) as COURSE_NAME,max([PP1]) as LOCATION,max([PPP1]) as SCORE,
    max([2]) as ABV_NAME,max([P2]) as COURSE_NAME,max([PP2]) as LOCATION,max([PPP2]) as SCORE
    from  (
    select *,
    ROW_NUMBER()OVER(partition by Name Order by COURSE_NAME ) P,
    'P'+CAST(ROW_NUMBER()OVER(partition by Name Order by COURSE_NAME)AS VARCHAR) PP,
    'PP'+CAST(ROW_NUMBER()OVER(partition by Name Order by COURSE_NAME)AS VARCHAR) PPP,
    'PPP'+CAST(ROW_NUMBER()OVER(partition by Name Order by COURSE_NAME)AS VARCHAR) PPPP
    from @TT  )T
    PIVOT (MAX(ABV_NAME) FOR P IN  ([1],[2])) AS P
    PIVOT (MAX(COURSE_NAME) FOR PP IN ([P1],[P2])) AS P
    PIVOT (MAX(LOCATION) FOR PPP IN ([PP1],[PP2])) AS P
    PIVOT (MAX(SCORE) FOR PPPP IN ([PPP1],[PPP2])) AS P
    group by NAME
    /*
    NAME            ABV_NAME   COURSE_NAME    LOCATION     SCORE   ABV_NAME   COURSE_NAME   LOCATION        SCORE
    --------------- ---------- -------------- ------------ ------- ---------- ------------- --------------- -----------
    BOB SMITH       18-19      ELA            NORTH HIGH   525     17-18      ELA           SOUTH MIDDLE    528
    */
    
    -------the second one
    ---------dynamic code
    go
    DECLARE @TT TABLE (
    NAME varchar(50),
    ABV_NAME varchar(10),
    COURSE_NAME varchar(50),
    LOCATION varchar(50),
    SCORE numeric(10,0)
    );
    INSERT INTO @TT VALUES
    ('BOB SMITH','18-19','ELA','NORTH HIGH','525'),
    ('BOB SMITH','17-18','ELA','SOUTH MIDDLE','528');
    
    DECLARE @sql_str VARCHAR(max)
    DECLARE @sql_col VARCHAR(max)
    DECLARE @sql_colp VARCHAR(max)
    DECLARE @sql_colpp VARCHAR(max)
    DECLARE @sql_colppp VARCHAR(max)
    DECLARE @sql_colstring VARCHAR(max)
    DECLARE @sql VARCHAR(max)
    
    
    DECLARE @max_vaue int=(select max(rn) from (select ROW_NUMBER()OVER(partition by Name Order by COURSE_NAME) as rn  from @TT) t  )
    declare @i int=1
    
    while (@i<=@max_vaue)
    begin
    SELECT @sql_col =ISNULL(@sql_col + ',','')+ QUOTENAME(@i) 
    SELECT @sql_colp =ISNULL(@sql_colp + ',','')+ QUOTENAME('P'+cast (@i as varchar(10))) 
    SELECT @sql_colpp =ISNULL(@sql_colpp + ',','')+ QUOTENAME('PP'+cast (@i as varchar(10))) 
    SELECT @sql_colppp =ISNULL(@sql_colppp + ',','')+ QUOTENAME('PPP'+cast (@i as varchar(10))) 
    SELECT @sql_colstring=ISNULL(@sql_colstring + ',','')+ 'max('+QUOTENAME(cast (@i as varchar(10)))
    +') AS  ABV_NAME,max('+QUOTENAME('P'+cast (@i as varchar(10)))
    +') as COURSE_NAME,max('+QUOTENAME('PP'+cast (@i as varchar(10)))
    +') as LOCATION,max('+QUOTENAME('PPP'+cast (@i as varchar(10)))+') as SCORE'
    set @i=@i+1
    end
    
    set @sql = '
    DECLARE @TT TABLE (
    NAME varchar(50),
    ABV_NAME varchar(10),
    COURSE_NAME varchar(50),
    LOCATION varchar(50),
    SCORE numeric(10,0)
    );
    INSERT INTO @TT VALUES
    (''BOB SMITH'',''18-19'',''ELA'',''NORTH HIGH'',''525''),
    (''BOB SMITH'',''17-18'',''ELA'',''SOUTH MIDDLE'',''528'');
    Select NAME,'+@sql_colstring+'
    from  (
    select *,
    ROW_NUMBER()OVER(partition by Name Order by COURSE_NAME ) P,
    ''P''+CAST(ROW_NUMBER()OVER(partition by Name Order by COURSE_NAME)AS VARCHAR) PP,
    ''PP''+CAST(ROW_NUMBER()OVER(partition by Name Order by COURSE_NAME)AS VARCHAR) PPP,
    ''PPP''+CAST(ROW_NUMBER()OVER(partition by Name Order by COURSE_NAME)AS VARCHAR) PPPP
    from @TT  )T
    PIVOT (MAX(ABV_NAME) FOR P IN  ('+@sql_col+')) AS P
    PIVOT (MAX(COURSE_NAME) FOR PP IN ('+@sql_colp+')) AS P
    PIVOT (MAX(LOCATION) FOR PPP IN ('+@sql_colpp+')) AS P
    PIVOT (MAX(SCORE) FOR PPPP IN ('+@sql_colppp+')) AS P
    group by NAME
    '
    print (@sql)
    exec (@sql)
    /*
    DECLARE @TT TABLE (
    NAME varchar(50),
    ABV_NAME varchar(10),
    COURSE_NAME varchar(50),
    LOCATION varchar(50),
    SCORE numeric(10,0)
    );
    INSERT INTO @TT VALUES
    ('BOB SMITH','18-19','ELA','NORTH HIGH','525'),
    ('BOB SMITH','17-18','ELA','SOUTH MIDDLE','528');
    Select NAME,max([1]) AS  ABV_NAME,max([P1]) as COURSE_NAME,max([PP1]) as LOCATION,max([PPP1]) as SCORE,max([2]) AS  ABV_NAME,max([P2]) as COURSE_NAME,max([PP2]) as LOCATION,max([PPP2]) as SCORE
    from  (
    select *,
    ROW_NUMBER()OVER(partition by Name Order by COURSE_NAME ) P,
    'P'+CAST(ROW_NUMBER()OVER(partition by Name Order by COURSE_NAME)AS VARCHAR) PP,
    'PP'+CAST(ROW_NUMBER()OVER(partition by Name Order by COURSE_NAME)AS VARCHAR) PPP,
    'PPP'+CAST(ROW_NUMBER()OVER(partition by Name Order by COURSE_NAME)AS VARCHAR) PPPP
    from @TT  )T
    PIVOT (MAX(ABV_NAME) FOR P IN  ([1],[2])) AS P
    PIVOT (MAX(COURSE_NAME) FOR PP IN ([P1],[P2])) AS P
    PIVOT (MAX(LOCATION) FOR PPP IN ([PP1],[PP2])) AS P
    PIVOT (MAX(SCORE) FOR PPPP IN ([PPP1],[PPP2])) AS P
    group by NAME
    NAME            ABV_NAME   COURSE_NAME    LOCATION     SCORE   ABV_NAME   COURSE_NAME   LOCATION        SCORE
    --------------- ---------- -------------- ------------ ------- ---------- ------------- --------------- -----------
    BOB SMITH       18-19      ELA            NORTH HIGH   525     17-18      ELA           SOUTH MIDDLE    528
    */
    

    Hope it will help you.

    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.

    Friday, November 8, 2019 2:52 AM
  • DECLARE @TT TABLE (
    NAME varchar(50),
    ABV_NAME varchar(10),
    COURSE_NAME varchar(50),
    LOCATION varchar(50),
    SCORE numeric(10,0)
    );
    
    
    
    INSERT INTO @TT VALUES
    ('BOB SMITH','18-19','ELA','NORTH HIGH','525'),
    ('BOB SMITH','17-18','ELA','SOUTH MIDDLE','528');
    
    ;with mycte as (
    SELECT NAME  ,
    ABV_NAME ,
    COURSE_NAME ,
    LOCATION ,
    SCORE , row_number() Over(partition by Name Order by score) rn  FROM @TT
    
    )
    
    select m1.NAME  ,
    m1.ABV_NAME ,
    m1.COURSE_NAME ,
    m1.LOCATION ,
    m1.SCORE, 
    m2.ABV_NAME  ABV_NAME2,
    m2.COURSE_NAME COURSE_NAME2,
    m2.LOCATION LOCATION2,
    m2.SCORE SCORE2
     from mycte m1 join mycte m2 
     on m1.NAME=m2.NAME and m1.rn=m2.rn-1 

    I am trying to accomplish the above example with thousands of records.  I am running into issues where some records (Names) are dropping out.  How would it be written if the data set was like this?  I want one row for each name.  Thank you

    INSERT INTO @TT VALUES
    ('RICK JONES','18-19','ELA','SOUTH MIDDLE','515'),
    ('RICK JONES','17-18','ELA','SOUTH MIDDLE','520'),
    ('BOB SMITH','18-19','ELA','NORTH HIGH','525'),
    ('BOB SMITH','17-18','ELA','SOUTH MIDDLE','528'),
    ('BOB SMITH','16-17','ELA','SOUTH MIDDLE','529'),
    ('KYLE SMITH','18-19','ELA','SOUTH MIDDLE','515'),
    ('KYLE SMITH','17-18','ELA','SOUTH MIDDLE','520'),
    ('KYLE SMITH','16-17','ELA','SOUTH MIDDLE','521'),
    ('KYLE SMITH','15-16','ELA','SOUTH MIDDLE','523');

    Friday, November 8, 2019 9:24 PM
  • Check this:

    select [NAME], STRING_AGG( CONCAT_WS( ' ', ABV_NAME, COURSE_NAME, [LOCATION], SCORE), ' ') within group (order by ABV_NAME) from @TT
    group by [NAME]
    order by [NAME]
    

    Friday, November 8, 2019 9:35 PM
  • Check this:

    select [NAME], STRING_AGG( CONCAT_WS( ' ', ABV_NAME, COURSE_NAME, [LOCATION], SCORE), ' ') within group (order by ABV_NAME) from @TT
    group by [NAME]
    order by [NAME]

    I get the following error

    Msg 195, Level 15, State 10, Line 1
    'CONCAT_WS' is not a recognized built-in function name.

    Friday, November 8, 2019 9:45 PM
  • Hi , 

    Please try my script.

    ---------dynamic code
    go
    DECLARE @TT TABLE (
    NAME varchar(50),
    ABV_NAME varchar(10),
    COURSE_NAME varchar(50),
    LOCATION varchar(50),
    SCORE numeric(10,0)
    );
    INSERT INTO @TT VALUES
    ('RICK JONES','18-19','ELA','SOUTH MIDDLE','515'),
    ('RICK JONES','17-18','ELA','SOUTH MIDDLE','520'),
    ('BOB SMITH','18-19','ELA','NORTH HIGH','525'),
    ('BOB SMITH','17-18','ELA','SOUTH MIDDLE','528'),
    ('BOB SMITH','16-17','ELA','SOUTH MIDDLE','529'),
    ('KYLE SMITH','18-19','ELA','SOUTH MIDDLE','515'),
    ('KYLE SMITH','17-18','ELA','SOUTH MIDDLE','520'),
    ('KYLE SMITH','16-17','ELA','SOUTH MIDDLE','521'),
    ('KYLE SMITH','15-16','ELA','SOUTH MIDDLE','523');
    
    DECLARE @sql_str VARCHAR(max)
    DECLARE @sql_col VARCHAR(max)
    DECLARE @sql_colp VARCHAR(max)
    DECLARE @sql_colpp VARCHAR(max)
    DECLARE @sql_colppp VARCHAR(max)
    DECLARE @sql_colstring VARCHAR(max)
    DECLARE @sql VARCHAR(max)
    
    
    DECLARE @max_vaue int=(select max(rn) from (select ROW_NUMBER()OVER(partition by Name Order by COURSE_NAME) as rn  from @TT) t  )
    declare @i int=1
    
    while (@i<=@max_vaue)
    begin
    SELECT @sql_col =ISNULL(@sql_col + ',','')+ QUOTENAME(@i) 
    SELECT @sql_colp =ISNULL(@sql_colp + ',','')+ QUOTENAME('P'+cast (@i as varchar(10))) 
    SELECT @sql_colpp =ISNULL(@sql_colpp + ',','')+ QUOTENAME('PP'+cast (@i as varchar(10))) 
    SELECT @sql_colppp =ISNULL(@sql_colppp + ',','')+ QUOTENAME('PPP'+cast (@i as varchar(10))) 
    SELECT @sql_colstring=ISNULL(@sql_colstring + ',','')+ 'max('+QUOTENAME(cast (@i as varchar(10)))
    +') AS  ABV_NAME,max('+QUOTENAME('P'+cast (@i as varchar(10)))
    +') as COURSE_NAME,max('+QUOTENAME('PP'+cast (@i as varchar(10)))
    +') as LOCATION,max('+QUOTENAME('PPP'+cast (@i as varchar(10)))+') as SCORE'
    set @i=@i+1
    end
    
    set @sql = '
    DECLARE @TT TABLE (
    NAME varchar(50),
    ABV_NAME varchar(10),
    COURSE_NAME varchar(50),
    LOCATION varchar(50),
    SCORE numeric(10,0)
    );
    INSERT INTO @TT VALUES
    (''RICK JONES'',''18-19'',''ELA'',''SOUTH MIDDLE'',''515''),
    (''RICK JONES'',''17-18'',''ELA'',''SOUTH MIDDLE'',''520''),
    (''BOB SMITH'',''18-19'',''ELA'',''NORTH HIGH'',''525''),
    (''BOB SMITH'',''17-18'',''ELA'',''SOUTH MIDDLE'',''528''),
    (''BOB SMITH'',''16-17'',''ELA'',''SOUTH MIDDLE'',''529''),
    (''KYLE SMITH'',''18-19'',''ELA'',''SOUTH MIDDLE'',''515''),
    (''KYLE SMITH'',''17-18'',''ELA'',''SOUTH MIDDLE'',''520''),
    (''KYLE SMITH'',''16-17'',''ELA'',''SOUTH MIDDLE'',''521''),
    (''KYLE SMITH'',''15-16'',''ELA'',''SOUTH MIDDLE'',''523'');
    Select NAME,'+@sql_colstring+'
    from  (
    select *,
    ROW_NUMBER()OVER(partition by Name Order by COURSE_NAME ) P,
    ''P''+CAST(ROW_NUMBER()OVER(partition by Name Order by COURSE_NAME)AS VARCHAR) PP,
    ''PP''+CAST(ROW_NUMBER()OVER(partition by Name Order by COURSE_NAME)AS VARCHAR) PPP,
    ''PPP''+CAST(ROW_NUMBER()OVER(partition by Name Order by COURSE_NAME)AS VARCHAR) PPPP
    from @TT  )T
    PIVOT (MAX(ABV_NAME) FOR P IN  ('+@sql_col+')) AS P
    PIVOT (MAX(COURSE_NAME) FOR PP IN ('+@sql_colp+')) AS P
    PIVOT (MAX(LOCATION) FOR PPP IN ('+@sql_colpp+')) AS P
    PIVOT (MAX(SCORE) FOR PPPP IN ('+@sql_colppp+')) AS P
    group by NAME
    '
    print (@sql)
    exec (@sql)
    /*
    DECLARE @TT TABLE (
    NAME varchar(50),
    ABV_NAME varchar(10),
    COURSE_NAME varchar(50),
    LOCATION varchar(50),
    SCORE numeric(10,0)
    );
    INSERT INTO @TT VALUES
    ('RICK JONES','18-19','ELA','SOUTH MIDDLE','515'),
    ('RICK JONES','17-18','ELA','SOUTH MIDDLE','520'),
    ('BOB SMITH','18-19','ELA','NORTH HIGH','525'),
    ('BOB SMITH','17-18','ELA','SOUTH MIDDLE','528'),
    ('BOB SMITH','16-17','ELA','SOUTH MIDDLE','529'),
    ('KYLE SMITH','18-19','ELA','SOUTH MIDDLE','515'),
    ('KYLE SMITH','17-18','ELA','SOUTH MIDDLE','520'),
    ('KYLE SMITH','16-17','ELA','SOUTH MIDDLE','521'),
    ('KYLE SMITH','15-16','ELA','SOUTH MIDDLE','523');
    Select NAME,max([1]) AS  ABV_NAME,max([P1]) as COURSE_NAME,max([PP1]) as LOCATION,max([PPP1]) as SCORE,max([2]) AS  ABV_NAME,max([P2]) as COURSE_NAME,max([PP2]) as LOCATION,max([PPP2]) as SCORE,max([3]) AS  ABV_NAME,max([P3]) as COURSE_NAME,max([PP3]) as LOCATION,max([PPP3]) as SCORE,max([4]) AS  ABV_NAME,max([P4]) as COURSE_NAME,max([PP4]) as LOCATION,max([PPP4]) as SCORE
    from  (
    select *,
    ROW_NUMBER()OVER(partition by Name Order by COURSE_NAME ) P,
    'P'+CAST(ROW_NUMBER()OVER(partition by Name Order by COURSE_NAME)AS VARCHAR) PP,
    'PP'+CAST(ROW_NUMBER()OVER(partition by Name Order by COURSE_NAME)AS VARCHAR) PPP,
    'PPP'+CAST(ROW_NUMBER()OVER(partition by Name Order by COURSE_NAME)AS VARCHAR) PPPP
    from @TT  )T
    PIVOT (MAX(ABV_NAME) FOR P IN  ([1],[2],[3],[4])) AS P
    PIVOT (MAX(COURSE_NAME) FOR PP IN ([P1],[P2],[P3],[P4])) AS P
    PIVOT (MAX(LOCATION) FOR PPP IN ([PP1],[PP2],[PP3],[PP4])) AS P
    PIVOT (MAX(SCORE) FOR PPPP IN ([PPP1],[PPP2],[PPP3],[PPP4])) AS P
    group by NAME
    
    (9 rows affected)
    NAME                                               ABV_NAME   COURSE_NAME                                        LOCATION                                           SCORE                                   ABV_NAME   COURSE_NAME                                        LOCATION                                           SCORE                                   ABV_NAME   COURSE_NAME                                        LOCATION                                           SCORE                                   ABV_NAME   COURSE_NAME                                        LOCATION                                           SCORE
    -------------------------------------------------- ---------- -------------------------------------------------- -------------------------------------------------- --------------------------------------- ---------- -------------------------------------------------- -------------------------------------------------- --------------------------------------- ---------- -------------------------------------------------- -------------------------------------------------- --------------------------------------- ---------- -------------------------------------------------- -------------------------------------------------- ---------------------------------------
    BOB SMITH                                          18-19      ELA                                                NORTH HIGH                                         525                                     17-18      ELA                                                SOUTH MIDDLE                                       528                                     16-17      ELA                                                SOUTH MIDDLE                                       529                                     NULL       NULL                                               NULL                                               NULL
    KYLE SMITH                                         18-19      ELA                                                SOUTH MIDDLE                                       515                                     17-18      ELA                                                SOUTH MIDDLE                                       520                                     16-17      ELA                                                SOUTH MIDDLE                                       521                                     15-16      ELA                                                SOUTH MIDDLE                                       523
    RICK JONES                                         18-19      ELA                                                SOUTH MIDDLE                                       515                                     17-18      ELA                                                SOUTH MIDDLE                                       520                                     NULL       NULL                                               NULL                                               NULL                                    NULL       NULL                                               NULL                                               NULL
    */

    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.

    • Marked as answer by skiggity100 Monday, November 11, 2019 8:08 PM
    Monday, November 11, 2019 8:30 AM
  • Hi , 

    Please try my script.

    ---------dynamic code
    go
    DECLARE @TT TABLE (
    NAME varchar(50),
    ABV_NAME varchar(10),
    COURSE_NAME varchar(50),
    LOCATION varchar(50),
    SCORE numeric(10,0)
    );
    INSERT INTO @TT VALUES
    ('RICK JONES','18-19','ELA','SOUTH MIDDLE','515'),
    ('RICK JONES','17-18','ELA','SOUTH MIDDLE','520'),
    ('BOB SMITH','18-19','ELA','NORTH HIGH','525'),
    ('BOB SMITH','17-18','ELA','SOUTH MIDDLE','528'),
    ('BOB SMITH','16-17','ELA','SOUTH MIDDLE','529'),
    ('KYLE SMITH','18-19','ELA','SOUTH MIDDLE','515'),
    ('KYLE SMITH','17-18','ELA','SOUTH MIDDLE','520'),
    ('KYLE SMITH','16-17','ELA','SOUTH MIDDLE','521'),
    ('KYLE SMITH','15-16','ELA','SOUTH MIDDLE','523');
    
    DECLARE @sql_str VARCHAR(max)
    DECLARE @sql_col VARCHAR(max)
    DECLARE @sql_colp VARCHAR(max)
    DECLARE @sql_colpp VARCHAR(max)
    DECLARE @sql_colppp VARCHAR(max)
    DECLARE @sql_colstring VARCHAR(max)
    DECLARE @sql VARCHAR(max)
    
    
    DECLARE @max_vaue int=(select max(rn) from (select ROW_NUMBER()OVER(partition by Name Order by COURSE_NAME) as rn  from @TT) t  )
    declare @i int=1
    
    while (@i<=@max_vaue)
    begin
    SELECT @sql_col =ISNULL(@sql_col + ',','')+ QUOTENAME(@i) 
    SELECT @sql_colp =ISNULL(@sql_colp + ',','')+ QUOTENAME('P'+cast (@i as varchar(10))) 
    SELECT @sql_colpp =ISNULL(@sql_colpp + ',','')+ QUOTENAME('PP'+cast (@i as varchar(10))) 
    SELECT @sql_colppp =ISNULL(@sql_colppp + ',','')+ QUOTENAME('PPP'+cast (@i as varchar(10))) 
    SELECT @sql_colstring=ISNULL(@sql_colstring + ',','')+ 'max('+QUOTENAME(cast (@i as varchar(10)))
    +') AS  ABV_NAME,max('+QUOTENAME('P'+cast (@i as varchar(10)))
    +') as COURSE_NAME,max('+QUOTENAME('PP'+cast (@i as varchar(10)))
    +') as LOCATION,max('+QUOTENAME('PPP'+cast (@i as varchar(10)))+') as SCORE'
    set @i=@i+1
    end
    
    set @sql = '
    DECLARE @TT TABLE (
    NAME varchar(50),
    ABV_NAME varchar(10),
    COURSE_NAME varchar(50),
    LOCATION varchar(50),
    SCORE numeric(10,0)
    );
    INSERT INTO @TT VALUES
    (''RICK JONES'',''18-19'',''ELA'',''SOUTH MIDDLE'',''515''),
    (''RICK JONES'',''17-18'',''ELA'',''SOUTH MIDDLE'',''520''),
    (''BOB SMITH'',''18-19'',''ELA'',''NORTH HIGH'',''525''),
    (''BOB SMITH'',''17-18'',''ELA'',''SOUTH MIDDLE'',''528''),
    (''BOB SMITH'',''16-17'',''ELA'',''SOUTH MIDDLE'',''529''),
    (''KYLE SMITH'',''18-19'',''ELA'',''SOUTH MIDDLE'',''515''),
    (''KYLE SMITH'',''17-18'',''ELA'',''SOUTH MIDDLE'',''520''),
    (''KYLE SMITH'',''16-17'',''ELA'',''SOUTH MIDDLE'',''521''),
    (''KYLE SMITH'',''15-16'',''ELA'',''SOUTH MIDDLE'',''523'');
    Select NAME,'+@sql_colstring+'
    from  (
    select *,
    ROW_NUMBER()OVER(partition by Name Order by COURSE_NAME ) P,
    ''P''+CAST(ROW_NUMBER()OVER(partition by Name Order by COURSE_NAME)AS VARCHAR) PP,
    ''PP''+CAST(ROW_NUMBER()OVER(partition by Name Order by COURSE_NAME)AS VARCHAR) PPP,
    ''PPP''+CAST(ROW_NUMBER()OVER(partition by Name Order by COURSE_NAME)AS VARCHAR) PPPP
    from @TT  )T
    PIVOT (MAX(ABV_NAME) FOR P IN  ('+@sql_col+')) AS P
    PIVOT (MAX(COURSE_NAME) FOR PP IN ('+@sql_colp+')) AS P
    PIVOT (MAX(LOCATION) FOR PPP IN ('+@sql_colpp+')) AS P
    PIVOT (MAX(SCORE) FOR PPPP IN ('+@sql_colppp+')) AS P
    group by NAME
    '
    print (@sql)
    exec (@sql)
    /*
    DECLARE @TT TABLE (
    NAME varchar(50),
    ABV_NAME varchar(10),
    COURSE_NAME varchar(50),
    LOCATION varchar(50),
    SCORE numeric(10,0)
    );
    INSERT INTO @TT VALUES
    ('RICK JONES','18-19','ELA','SOUTH MIDDLE','515'),
    ('RICK JONES','17-18','ELA','SOUTH MIDDLE','520'),
    ('BOB SMITH','18-19','ELA','NORTH HIGH','525'),
    ('BOB SMITH','17-18','ELA','SOUTH MIDDLE','528'),
    ('BOB SMITH','16-17','ELA','SOUTH MIDDLE','529'),
    ('KYLE SMITH','18-19','ELA','SOUTH MIDDLE','515'),
    ('KYLE SMITH','17-18','ELA','SOUTH MIDDLE','520'),
    ('KYLE SMITH','16-17','ELA','SOUTH MIDDLE','521'),
    ('KYLE SMITH','15-16','ELA','SOUTH MIDDLE','523');
    Select NAME,max([1]) AS  ABV_NAME,max([P1]) as COURSE_NAME,max([PP1]) as LOCATION,max([PPP1]) as SCORE,max([2]) AS  ABV_NAME,max([P2]) as COURSE_NAME,max([PP2]) as LOCATION,max([PPP2]) as SCORE,max([3]) AS  ABV_NAME,max([P3]) as COURSE_NAME,max([PP3]) as LOCATION,max([PPP3]) as SCORE,max([4]) AS  ABV_NAME,max([P4]) as COURSE_NAME,max([PP4]) as LOCATION,max([PPP4]) as SCORE
    from  (
    select *,
    ROW_NUMBER()OVER(partition by Name Order by COURSE_NAME ) P,
    'P'+CAST(ROW_NUMBER()OVER(partition by Name Order by COURSE_NAME)AS VARCHAR) PP,
    'PP'+CAST(ROW_NUMBER()OVER(partition by Name Order by COURSE_NAME)AS VARCHAR) PPP,
    'PPP'+CAST(ROW_NUMBER()OVER(partition by Name Order by COURSE_NAME)AS VARCHAR) PPPP
    from @TT  )T
    PIVOT (MAX(ABV_NAME) FOR P IN  ([1],[2],[3],[4])) AS P
    PIVOT (MAX(COURSE_NAME) FOR PP IN ([P1],[P2],[P3],[P4])) AS P
    PIVOT (MAX(LOCATION) FOR PPP IN ([PP1],[PP2],[PP3],[PP4])) AS P
    PIVOT (MAX(SCORE) FOR PPPP IN ([PPP1],[PPP2],[PPP3],[PPP4])) AS P
    group by NAME
    
    (9 rows affected)
    NAME                                               ABV_NAME   COURSE_NAME                                        LOCATION                                           SCORE                                   ABV_NAME   COURSE_NAME                                        LOCATION                                           SCORE                                   ABV_NAME   COURSE_NAME                                        LOCATION                                           SCORE                                   ABV_NAME   COURSE_NAME                                        LOCATION                                           SCORE
    -------------------------------------------------- ---------- -------------------------------------------------- -------------------------------------------------- --------------------------------------- ---------- -------------------------------------------------- -------------------------------------------------- --------------------------------------- ---------- -------------------------------------------------- -------------------------------------------------- --------------------------------------- ---------- -------------------------------------------------- -------------------------------------------------- ---------------------------------------
    BOB SMITH                                          18-19      ELA                                                NORTH HIGH                                         525                                     17-18      ELA                                                SOUTH MIDDLE                                       528                                     16-17      ELA                                                SOUTH MIDDLE                                       529                                     NULL       NULL                                               NULL                                               NULL
    KYLE SMITH                                         18-19      ELA                                                SOUTH MIDDLE                                       515                                     17-18      ELA                                                SOUTH MIDDLE                                       520                                     16-17      ELA                                                SOUTH MIDDLE                                       521                                     15-16      ELA                                                SOUTH MIDDLE                                       523
    RICK JONES                                         18-19      ELA                                                SOUTH MIDDLE                                       515                                     17-18      ELA                                                SOUTH MIDDLE                                       520                                     NULL       NULL                                               NULL                                               NULL                                    NULL       NULL                                               NULL                                               NULL
    */

    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.

    Thank you so much for your help Rachel.  This is exactly what I needed.
    Monday, November 11, 2019 8:07 PM