Presenting rows of data horizontally

  • I have two fields in a SQL Server 2008 view.

    ID and SubID. There are up to 24 subIDs for any ID.

    In other words, there can be up to 24 rows for an ID field.

    I want to present these across as below:

    ID SubID-1 SubID-2...and so on with null in extra subID columns.

    Is there a way to do this using a SQL query?

    I am a newbie, so any help would be greatly appreciated. Thanks much.

  • smitausa (6/27/2012)


    I have two fields in a SQL Server 2008 view.

    ID and SubID. There are up to 24 subIDs for any ID.

    In other words, there can be up to 24 rows for an ID field.

    I want to present these across as below:

    ID SubID-1 SubID-2...and so on with null in extra subID columns.

    Is there a way to do this using a SQL query?

    I am a newbie, so any help would be greatly appreciated. Thanks much.

    There is a method, in fact more than one method to do so. But to assist you, you should assist us.

    Please post your table definition, some sample data and a what you desire using the sample data.

    Now to do the above please click on the first link in my signature block. The article explains how to do what I have requested and includes sample T-SQL to allow you to do it, rather quickly and simply.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • The method chosen (adding to what Ron said) will partly depend on the information you can provide back.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • The table is as below

    [id] [subid] [pct]

    abc w 10

    abc e 20

    abc g 70

    def a 100

    ghi x 20

    ghi h 80

    jkl p null

    jkl l 1

    jkl m null

    The output should look like below.

    abc w 10 e 20 g 70

    def a 100

    ghi x 20 h 80

    jkl p null l 1 m null

    Sorry, it is my post here. Thanks.

  • tinausa (6/27/2012)


    The table is as below

    [id] [subid] [pct]

    abc w 10

    abc e 20

    abc g 70

    def a 100

    ghi x 20

    ghi h 80

    jkl p null

    jkl l 1

    jkl m null

    The output should look like below.

    abc w 10 e 20 g 70

    def a 100

    ghi x 20 h 80

    jkl p null l 1 m null

    Sorry, it is my post here. Thanks.

    There is a solution to a similar problem here: http://www.sqlservercentral.com/Forums/Topic1319158-391-1.aspx#bm1319226

    You can also search PIVOT and crosstab on the forum to find what you're looking for.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Thanks, I will check out the link.

    I saw various solutions but most of them are not applicable in my case since the id and subid columns have unique values of 18 characters each. They do not repeat.

    So the output has to have columns such as

    id subid1 pct1 subid2 pct2 and so on.

    Thanks.

  • tinausa (6/27/2012)


    The table is as below

    [id] [subid] [pct]

    abc w 10

    abc e 20

    abc g 70

    def a 100

    ghi x 20

    ghi h 80

    jkl p null

    jkl l 1

    jkl m null

    The output should look like below.

    abc w 10 e 20 g 70

    def a 100

    ghi x 20 h 80

    jkl p null l 1 m null

    Sorry, it is my post here. Thanks.

    Please see the first link in my signature line below to get the best help the quickest.

    For your output, are those supposed to be in columns or all concatenated into a single column?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks Jeff, I need them to be in columns - a table actually.

    Tina

  • tinausa (6/28/2012)


    Thanks Jeff, I need them to be in columns - a table actually.

    Tina

    Since there are an unknown number of potential columns, the following article tells you how to do it.

    http://www.sqlservercentral.com/articles/Crosstab/65048/

    If you'd like a coded answer, it would be helpful if the data you presented were in a readily consumable format. Please see the first link in my signature line below for how to do that properly.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • It can be done in a no. of ways depending on whether the no. of columns in the query would be static or Dynamic. Following are a few ways of doing it:

    --Creating Tables

    Create Table Ex

    (id Char(3),

    subid Char(1),

    pct int )

    --Inserting Sample Data

    Insert Into Ex

    Select 'abc', 'w', 10

    Union ALL

    Select 'abc', 'e', 20

    Union ALL

    Select 'abc', 'g', 70

    Union ALL

    Select 'def', 'a', 100

    Union ALL

    Select 'ghi', 'x', 20

    Union ALL

    Select 'ghi', 'h', 80

    Union ALL

    Select 'jkl', 'p', null

    Union ALL

    Select 'jkl', 'l', 1

    Union ALL

    Select 'jkl', 'm', null

    --Query With Case

    Select id, Max(subid1) As subid1, Max(pct1) As pct1, Max(subid1) As subid1, Max(pct2) As pct2, Max(subid3) As subid3, Max(pct3) As pct3 From

    (Select id,

    (Case When rn = 1 Then subid Else '' End) As subid1,

    (Case When rn = 1 Then subid Else '' End) As pct1,

    (Case When rn = 2 Then subid Else '' End) As subid2,

    (Case When rn = 2 Then subid Else '' End) As pct2,

    (Case When rn = 3 Then subid Else '' End) As subid3,

    (Case When rn = 3 Then subid Else '' End) As pct3

    From

    (Select *, Row_number() Over (Partition By id Order By id) As rn From Ex) As a ) As b

    Group By id

    --Static Pivot

    Select id, IsNULL(Max(subid1), '') As subid1, IsNULL(Max(subid2), '') As subid2, IsNULL(Max(subid3), '') As subid3 From

    (Select id, [subid1], [subid2], [subid3] From

    (Select *, 'subid' + Cast(Row_Number() Over (Partition By id Order By id) As Varchar(10) ) As rn From Ex) As a

    Pivot

    (Max(subid) For rn IN ([subid1], [subid2], [subid3]) ) As pvt ) As b

    Group By id

    --Dynamic Pivot

    Declare @cols Varchar(max), @cols1 Varchar(max), @sql Varchar(max)

    Declare @temp Table(Cols Varchar(10) )

    Insert Into @temp

    Select Distinct 'SubId' + Cast(Row_number() Over (Partition By id Order By id) As Varchar(10) ) As rn From Ex

    Select @cols = Coalesce(@cols + ', ', '') + QuoteName(Cols) From @temp

    Select @cols1 = Coalesce(@cols1 + '), ''''), IsNULL(Max(', '') + QuoteName(Cols) From @temp

    Set @cols1 = 'IsNULL(Max(' + @cols1 + '), '''')'

    Set @sql = 'Select id, '+@cols1+' From

    (Select id, '+@cols+' From

    (Select *, ''subid'' + Cast(Row_Number() Over (Partition By id Order By id) As Varchar(10) ) As rn From Ex) As a

    Pivot

    (Max(subid) For rn IN ('+@cols+') ) As pvt ) As b

    Group By id'

    Execute (@sql)

    If the Columns would remain static then you can use any one of the Case Query or the Static Pivot.

    If the no. of columns being used by the query is bound to change then you can use the Dynamic Pivot.

    Hope this helps.

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • This is a very interesting quandary indeed. I am sure this can be done using a little less code but here is what I came up with:

    SET NOCOUNT ON

    GO

    /***********************************************************************

    1) Create the sample data

    ************************************************************************/

    DECLARE @srcData TABLE

    (

    [id]varchar(4),

    [subid]varchar(2),

    [pct]tinyint

    );

    INSERT INTO @srcData

    VALUES('abc','w', 10),

    ('abc','e', 20),

    ('abc','g', 70),

    ('def','a', 100),

    ('ghi','x', 20),

    ('ghi','h', 80),

    ('jkl','p', null),

    ('jkl','l', 1),

    ('jkl','m', null);

    /***********************************************************************

    2) Declare varibles & create temp tables for my iteration routine below

    Create temp tables:

    @Step1:To get a distinct list & count of ID's

    @Step2:Copy of the source data with (subid & pct concatinated)

    @out:temp table used for storing output

    ***********************************************************************/

    DECLARE @i int = 1, @i2 int, @ii int, @id varchar(4),

    @rows int, @results varchar(10), @stuff varchar(100) = ''

    DECLARE @step1 TABLE

    (

    intIDENTITY(1,1),

    [id]varchar(4),

    [rows]int

    );

    DECLARE @step2 TABLE

    (

    intIDENTITY(1,1),

    [id]varchar(4),

    [results]varchar(100)

    );

    DECLARE @out TABLE

    (

    [results]varchar(100)

    );

    /***********************************************************************

    3) Populate @step1 & @step2

    ***********************************************************************/

    INSERT INTO @step1 ([id],[rows])

    SELECTu.[id],

    u.[rows]

    FROM

    (

    SELECTu.[id],

    COUNT(u.[id]) AS [rows]

    FROM

    (

    SELECT DISTINCT [id]

    FROM @srcData

    ) u

    JOIN @srcData f ON u.id = f.id

    GROUP BY u.id

    ) u;

    SELECT * FROM @step1;

    INSERT INTO @step2 ([id],[results])

    SELECT[id],

    subid + ' ' + ISNULL(CAST(pct AS varchar(5)),'null') [x]

    FROM @srcData;

    SELECT '@step2' AS

    SELECT * FROM @step2;

    /***********************************************************************

    4) Iterate thorugh step1 & @step2 to put the final results into @out

    ***********************************************************************/

    SET @ii = (SELECT COUNT(*) FROM @step1);

    --For each DISTINCT (unique) ID

    WHILE @i <= @ii

    BEGIN

    SELECT @id =(SELECT [id] FROM @step1 WHERE = @i),

    @rows = (SELECT [rows] FROM @step1 WHERE = @i),

    @i2 = 1;

    SET @stuff = CAST(@id AS varchar(5))

    -- collect and concatinate the results

    WHILE @i2 <= @rows

    BEGIN

    SET @results = (SELECT TOP 1 results FROM @step2 WHERE id = @id);

    SET @stuff = @stuff + ' ' + @results;

    DELETE FROM @step2

    WHERE id = @id

    AND results = (SELECT TOP 1 results FROM @step2 WHERE id = @id);

    SET @i2 = @i2+1;

    END

    INSERT INTO @out

    SELECT @stuff

    SET @stuff = ''

    SET @i = @i+1

    END;

    /***********************************************************************

    5) Output

    ***********************************************************************/

    SELECT * FROM @out;

    GO

    Here's what is going on:

    1) We create the sample data

    2) Declare varibles & create temp tables for my iteration routine below

    What the temp tables do:

    @Step1:To get a distinct list & count of ID's

    @Step2:Copy of the source data with (subid & pct concatinated)

    @out:temp table used for storing output

    3) Populate @step1 and @step2

    4) Iterate thorugh step1 & @step2 to put the final results into @out

    Hope this helps!

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Hi everyone i have a very request regarding displaying rows in horizontal i want the columns name of a SQL server table displayed horizontally like following My query :
    NB AFPO is the name of the table i want to display columns  name from
    Also, i have a lot of tables to get the columns name displayed in Horizontal  so help would be appreciated

    My query: 
    select column_id, name from sys.all_columns where object_id = OBJECT_ID(N'AFPO') order by column_id

    displays :
    column_id    name
    1                  POSNR
    2                  MANDT
    3                  AUFNR
    4                  KDAUF

    And i want this to be displayed this way :

    column_id    1            2            3           4
    name           POSNR MANDT AUFNR KDAUF

    and no way to find out how to achieve that. Could you help me?


    Steph

  • Heslous - Friday, July 6, 2018 4:26 AM

    Hi everyone i have a very request regarding displaying rows in horizontal i want the columns name of a SQL server table displayed horizontally like following My query :
    NB AFPO is the name of the table i want to display columns  name from
    Also, i have a lot of tables to get the columns name displayed in Horizontal  so help would be appreciated

    My query: 
    select column_id, name from sys.all_columns where object_id = OBJECT_ID(N'AFPO') order by column_id

    displays :
    column_id    name
    1                  POSNR
    2                  MANDT
    3                  AUFNR
    4                  KDAUF

    And i want this to be displayed this way :

    column_id    1            2            3           4
    name           POSNR MANDT AUFNR KDAUF

    and no way to find out how to achieve that. Could you help me?

    6 year old topic 😉

    As for how to do this, have you had a look at the article that Jeff linked above? It should greatly help you get what you're after. Have a read and try yourself; if you get stuck please do post what you've tried so we can help you further.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Thom A - Friday, July 6, 2018 4:49 AM

    Heslous - Friday, July 6, 2018 4:26 AM

    Hi everyone i have a very request regarding displaying rows in horizontal i want the columns name of a SQL server table displayed horizontally like following My query :
    NB AFPO is the name of the table i want to display columns  name from
    Also, i have a lot of tables to get the columns name displayed in Horizontal  so help would be appreciated

    My query: 
    select column_id, name from sys.all_columns where object_id = OBJECT_ID(N'AFPO') order by column_id

    displays :
    column_id    name
    1                  POSNR
    2                  MANDT
    3                  AUFNR
    4                  KDAUF

    And i want this to be displayed this way :

    column_id    1            2            3           4
    name           POSNR MANDT AUFNR KDAUF

    and no way to find out how to achieve that. Could you help me?

    6 year old topic 😉

    As for how to do this, have you had a look at the article that Jeff linked above? It should greatly help you get what you're after. Have a read and try yourself; if you get stuck please do post what you've tried so we can help you further.

    Hi Thom
    i do not see the Jeff's  link you talk about


    Steph

  • create table #temp (id varchar(50),subid varchar(10),pcs varchar(10))

        insert into #temp select 'abc', 'w 10',NULL
        insert into #temp select 'abc', 'e 20',NULL
        insert into #temp select 'abc', 'g 70',NULL
        insert into #temp select 'def', 'a 100',Null
        insert into #temp select 'ghi', 'x 20',Null
        insert into #temp select 'ghi', 'h 80',Null
        insert into #temp select 'jkl', 'p',Null
        insert into #temp select 'jkl', 'l','1'
        insert into #temp select 'jkl', 'm', null

        Select id,stuff((select ''+subid,isnull(pcs,'')
                         from #temp t1
                         where t1.id=t2.id
                         for xml path ('')),1,1,'')
        from #temp t2
        group by id

    ***The first step is always the hardest *******

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply