How to convert vertical records to horizontal?

  • Could some please help me with how to write this query in T-SQL? Say there is a product table with just 1 column, ProductID, with the following rows:

    1

    2

    3

    4

    how to generate a string with one query (no cursor) to concat ProductID? i.e., in this case, to generate string '1234' with one query.

    Please help?

  • only if you have some unique identifier.

    select a.productid + b.productid + c.productid

     from Product a, product b, product c

     where a.productid = 'A' and b.productid = 'B' and c.productid = 'C'

     

  • I think Raymond needs the concat value rather than the sum

     

    SELECT     CAST(a.ID AS varchar(1)) + CAST(b.ID AS varchar(1)) + CAST(c.ID AS varchar(1)) + CAST(d.ID AS varchar(1)) AS Expr1

    FROM         MAINCATEGORY a CROSS JOIN

                          MAINCATEGORY b CROSS JOIN

                          MAINCATEGORY c CROSS JOIN

                          MAINCATEGORY d

    WHERE     (a.ID = 1) AND (b.ID = 2) AND (c.ID = 3) AND (d.ID = 4)




    My Blog: http://dineshasanka.spaces.live.com/

  • Denish/Steve,

    Thanks for the reply. That was actually an interview question that I was asked last week, and I came with something like:

    select CAST(a.ID AS varchar(1)) + CAST(b.ID AS varchar(1)) + CAST(c.ID AS varchar(1)) + CAST(d.ID AS varchar(1))

    FROM

    (select ID from product where id=1) a,

    (select ID from product where id=2) b,

    select ID from product where id=3) c,

    select ID from product where id=4) d

    As you can see, all our solutions seem to work, but we all rely on hard-code 1/2/3/4. So if the table has records from 1-100, ours will look very awkward.

    The interviewer mentioned to me the easiest solution something like:

    declard @x varchar(100)

    select @x=@x+CAST(ID as varchar(1)) FROM Product

    Print @X

    The idea was that SELECT will loop through all rows, but I could not get this working.

    Thoughts?

  • Raymond, you are on right track but you must remember that NULL concatentated with something is NULL.

    When you first assign value to @x, @x is NULL.

    Therefore you need to set @x = '' from the very beginning or use ISNULL.

    Also be careful with cast(1) as it only has enough space for numbers from 0 to 9.

    Using Northwind as an example...

    --- cut here ---

    USE NORTHWIND

    GO

    declare @x varchar(100)

    set @x = ''

    select @x = @x+ '/' +CAST(productid as varchar(2)) FROM Products

    WHERE PRODUCTID < 100

    Print @X

    --- cut here ---

    HTH

    Billy

  • If no scripting allowed, how about....

    select min(a.ProductID), min(b.ProductID), min(c.ProductID), min(d.ProductID)  
      from Table1 a
        left join Table1 b on b.ProductID > a.ProductID
        left join Table1 c on c.ProductID > b.ProductID
        left join Table1 d on d.ProductID > c.ProductID
  • Well, interview question or not. My answer would be

    Do this at the client! The server is for data retrieval, not presentational stuff.

     

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Frank,

    Don't be so quick to give up on the data layer.  I had a similar challenge... I've posted ALL the code including the example data and table creation.  The actual code that does the work you are looking for is very short.  If you don't need the delimiter, set the delimiter to '' and you'll be OK.

    --Jeff Moden

    Here's the code...

    SET NOCOUNT ON

    GO

    /*===== SETUP DEMONSTRATION FOR IMPROVED MAKEMODEL PROBLEM =====*/

    ----- DROP MAKEMODEL TABLE IF IT EXISTS

    IF EXISTS

     (SELECT * FROM SYSOBJECTS WHERE [NAME] = 'MakeModel')

     DROP TABLE dbo.MakeModel

    GO

    ----- IF THE NEW FUNCTION EXISTS, DROP IT -----

    IF EXISTS

     (SELECT * FROM SYSOBJECTS WHERE [NAME] = 'fn_DelimitRow')

     DROP FUNCTION dbo.fn_DelimitRow

    GO

    ----- CREATE THE MAKEMODEL TABLE -----

    CREATE TABLE dbo.MakeModel

     (

     [ID]    [int] IDENTITY (1, 1) NOT NULL ,

     [MAKE]  [varchar]      (10)       NULL ,

     [MODEL] [varchar]      (20)       NULL

    &nbsp ON [PRIMARY]

    GO

    ----- POPULATE THE MAKE MODEL TABLE WITH EXAMPLE DATA -----

    INSERT INTO MakeModel (MAKE,MODEL)

           VALUES ('FORD','MUSTANG')

    INSERT INTO MakeModel (MAKE,MODEL)

           VALUES ('CHEVY','MALIBU')

    INSERT INTO MakeModel (MAKE,MODEL)

           VALUES ('CHEVY','NOVA')

    INSERT INTO MakeModel (MAKE,MODEL)

           VALUES ('LINCOLN','CONTINENTAL')

    INSERT INTO MakeModel (MAKE,MODEL)

           VALUES ('FORD','NAVIGATOR')

    INSERT INTO MakeModel (MAKE,MODEL)

           VALUES ('FORD','F150 TRUCK')

    INSERT INTO MakeModel (MAKE,MODEL)

           VALUES ('GMC','SIERRA')

    INSERT INTO MakeModel (MAKE,MODEL)

           VALUES ('CHEVY','S10')

    GO

    ----- DISPLAY CONTENTS OF TABLE TO VERIFY

    SELECT *

      FROM MakeModel

    GO

    /*===== CREATE THE NEW FUNCTION =====*/

    ----- Could parameterize more for table and column names

    --    and use with dynamic SQL to run

    CREATE FUNCTION fn_DelimitRow (@Make VARCHAR(50),@Delim VARCHAR(10))

           RETURNS VARCHAR(8000)

    AS

    BEGIN

     DECLARE @info  VARCHAR(8000)

     

            --  Next line is "JBM PFM!"

     SELECT  @info = COALESCE(@Info + @Delim, '') + RTRIM(MODEL)

       FROM  MakeModel

      WHERE  MAKE=@Make

      ORDER BY  MODEL

     RETURN (@Info)

    END

    GO

    /*===== NOW, DEMO NEW MAKEMODEL PROBLEM SOLUTION =====*/

    ----- No temp or working tables and no cursors!

    ----- Can pass any 1-10 character delimiter in single quotes

    SELECT  DISTINCT MAKE, dbo.fn_DelimitRow(MAKE,', ') AS MODELS

      FROM  MakeModel

    --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)

  • if ProductID is a char, then try this:

    declare @string_var varchar(255)

    select @string_var = ""

    select @string_var = @string_var + ProductID from tbl_a

    print  @string_var

    When I first saw this, I could't believe it works. But it does. It must do its own recursion during execution....

  • Be careful... if any NULLS, answer will be NULL unless "Concatenate Nulls Yields Nulls" is turned off.

    --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)

  • Say I want the horizontal result to be individual columns instead of a concatenated string?  For example:

    CREATE TABLE

    dbo.MetaData

    (

    [ID] [int]

    IDENTITY (1, 1) NOT NULL ,

    [FieldID] [int]

    NULL ,

    [FieldName] [varchar] (50)

    NULL ,

    [FieldValue] [varchar] (50)

    NULL )

    GO

    ----- POPULATE THE META DATA TABLE WITH EXAMPLE DATA -----

    INSERT INTO

    MetaData (FieldID, FieldName, FieldValue)

    VALUES (10, 'Name','Tom')

    INSERT INTO

    MetaData (FieldID, FieldName, FieldValue)

    VALUES (10, 'Address','1234 S. King St.')

    INSERT INTO

    MetaData (FieldID, FieldName, FieldValue)

    VALUES (10, 'Zip','99012')

    INSERT INTO

    MetaData (FieldID, FieldName, FieldValue)

    VALUES (11, 'Name','Ken')

    INSERT INTO

    MetaData (FieldID, FieldName, FieldValue)

    VALUES (11, 'Address','2345 South St.')

    INSERT INTO

    MetaData (FieldID, FieldName, FieldValue)

    VALUES (11, 'Zip','99123')

    INSERT INTO

    MetaData (FieldID, FieldName, FieldValue)

    VALUES (12, 'Name','Matt')

    INSERT INTO

    MetaData (FieldID, FieldName, FieldValue)

    VALUES (12, 'Address','5678 Beretania St.')

    INSERT INTO

    MetaData (FieldID, FieldName, FieldValue)

    VALUES (12, 'Zip','99456')

    GO

     

    Now I want the result to be in indivdual columns (ID, Name, Value, etc) instead of a single column.

    Any ideas?

  • Can do...

     SELECT FieldID,

            MIN(CASE WHEN FieldName = 'NAME'    THEN FieldValue END) AS NAME,

            MIN(CASE WHEN FieldName = 'Address' THEN FieldValue END) AS Address,

            MIN(CASE WHEN FieldName = 'ZIP'     THEN FieldValue END) AS ZIP

       FROM MetaData

      GROUP BY FieldID

     

    --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)

Viewing 12 posts - 1 through 11 (of 11 total)

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