Post reply

Showing 2 parameters "in line" on one line of an output

  • February 11, 2019 at 2:00 am

    #376953

    Hi there

    I'm trying to work out how to show 2 or more parameters from one column on a line next to each other. What I mean is this...

    My database has a table called PARAMETER which can hold multiple incidences of CODE against a SERIALNUMBER. So, SERIALNUMBER 1 might have a CODE of CAT and DOG, and SERIALNUMBER 2 might have a CODE of CAT, DOG, HORSE etc.

    When I run a SQL script such as SELECT * FROM CODE WHERE SERIALNUMBER=2 then I will get 3 lines. However, I'd like to see the SERIALNUMBER show in one column with CAT, DOG, HORSE all on one line as the next column. Our database report writer can do this, but I can't see the SQL script behind it.

    Any help would be much appreciated.

    Thank you
    Jon

  • February 11, 2019 at 4:03 am

    #2021626

    It sounds like you might need to use the FOR XML clause. Would something like this help you? You can copy and paste the entire code in to a new query window to see how it runs:

    DECLARE @Table TABLE (fldSerialNumber int, fldCode varchar(10))

    INSERT INTO @Table (fldSerialNumber, fldCode) SELECT 1,'CAT'
    INSERT INTO @Table (fldSerialNumber, fldCode) SELECT 1,'DOG'
    INSERT INTO @Table (fldSerialNumber, fldCode) SELECT 2,'PARROT'
    INSERT INTO @Table (fldSerialNumber, fldCode) SELECT 2,'MOUSE'
    INSERT INTO @Table (fldSerialNumber, fldCode) SELECT 2,'FOX'

    SELECT * FROM @Table

    SELECT    fldSerialNumber
            ,STUFF((SELECT ', ' + fldCode FROM @Table AS [Inner] WHERE [Inner].fldSerialNumber = [Outer].fldSerialNumber FOR XML PATH('')),1,2,'') AS [fldCode]
    FROM @Table AS [Outer]
    GROUP BY fldSerialNumber

    The code will create a table variable and insert some dummy data (I'm assuming this is similar to the data you are describing). The first select will display the data you seem to be getting right now, and the second select uses the FOR XML and a GROUP BY to consolidate the values for CODE in to a single row.

    Rob

  • February 14, 2019 at 6:58 am

    #2022060

    Hi Rob
    Many thanks for your reply and apologies for the late response from me.
    That works great! However, I wish to produce an output for ALL serial numbers, so I wouldn't just want to specify 1 & 2 on their own. How would I adapt the script to output this for all contacts?
    Best wishes
    Jon

  • February 14, 2019 at 7:38 am

    #2022064

    j.clay 47557 - Thursday, February 14, 2019 6:58 AM

    Hi Rob
    Many thanks for your reply and apologies for the late response from me.
    That works great! However, I wish to produce an output for ALL serial numbers, so I wouldn't just want to specify 1 & 2 on their own. How would I adapt the script to output this for all contacts?
    Best wishes
    Jon

    You can just substitute your actual table name where Jon's code has @Table, leaving out all the INSERT stuff and just doing the SELECT.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • February 14, 2019 at 7:51 am

    #2022066

    Genius!! Thank you both.

  • February 14, 2019 at 7:55 am

    #2022069

    j.clay 47557 - Thursday, February 14, 2019 6:58 AM

    Hi Rob
    Many thanks for your reply and apologies for the late response from me.
    That works great! However, I wish to produce an output for ALL serial numbers, so I wouldn't just want to specify 1 & 2 on their own. How would I adapt the script to output this for all contacts?
    Best wishes
    Jon

    Try this (on it's own with none of the other code I provided):

    SELECT  SERIALNUMBER
       ,STUFF((SELECT ', ' + CODE FROM PARAMETER AS [Inner] WHERE [Inner].SERIALNUMBER= [Outer].SERIALNUMBER FOR XML PATH('')),1,2,'') AS
    FROM PARAMETER AS [Outer]
    GROUP BY SERIALNUMBER

  • February 14, 2019 at 8:00 am

    #2022072

    Thanks Rob, that works a treat 🙂

Viewing 7 posts - 1 through 6 (of 6 total)

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