Display 2 rows as 1 result in a column

  • Hi

    I have a table eg:

    TableName ColumnName

    ---------- -----------

    MyTable1 Col1

    MyTable1 Col2

    MyTable2 Col1

    MyTable2 Col2

    I would like to write a qeury that will display the following :

    MyTable1 Col1,Col2

    MyTable2 Col1,Col2

    etc

    Any pointers as to how I can do this?

    Thanks

    Mike

  • Check out the link to "String Concatenation" in my signature. It details quite a few different methods of doing this.

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • use PIVOT

    _____________________________________________
    One ounce of practice is more important than tonnes of dreams

  • Included the solution; You might wanna fiddle around it for different solution

    But there is more fun in finding the solution as @seth pointed. Also make sure you check the difference between the two data (col1,col2).

    CREATE TABLE #TEST (TableName VARCHAR(50), ColumnName VARCHAR(50))

    INSERT INTO #TEST

    SELECT 'MyTable1','Col1'

    UNION ALL

    SELECT 'MyTable1','Col2'

    UNION ALL

    SELECT 'MyTable2','Col1'

    UNION ALL

    SELECT 'MyTable2','Col2'

    SELECT TABLENAME ,

    (STUFF ((SELECT ',' + COLUMNNAME

    FROM #TEST T1

    where T1.TableName = t2.TableName

    FOR XML PATH('')),1,1,'')) Newvalues

    FROM #TEST T2

    group by TableName

  • Thanks Guys. That string concatenation doc is very useful.

    I knew I had a solution using for xml path but just couldn't get it right.

    Grasshopper, your query works like a charm thanks. 🙂

  • SpitFireXVII (4/1/2011)


    Thanks Guys. That string concatenation doc is very useful.

    I knew I had a solution using for xml path but just couldn't get it right.

    Grasshopper, your query works like a charm thanks. 🙂

    Grasshopper is not the name it is the level in SQLServerCentral.com

    Even Join can also be used but make sure that you are giving the data in pairs. if you are not giving the data data in pair the last data will be eliminate in this case.

    DECLARE @tbl TABLE

    ( col0 int identity,

    col1 CHAR(10), Col2 CHAR(10)

    )

    INSERT INTO @tbl

    SELECT 'MyTable1' ,'Col1' UNION ALL

    SELECT 'MyTable1' ,'Col2' UNION ALL

    SELECT 'MyTable2' ,'Col1' UNION ALL

    SELECT 'MyTable2' ,'Col2'

    SELECT t1.col1,t1.col2,t2.Col2

    FROM @tbl t1

    INNER JOIN @tbl t2 ON t1.col0=t2.col0-1 AND t1.col0%2=1

    Thanks
    Parthi

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

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