Display 2 rows as 1 result in a column

• SpitFireXVII

  SSC Veteran

  Points: 212

  More actions

  April 1, 2011 at 8:57 am

  #234158

  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

• Garadin

  One Orange Chip

  Points: 29613

  More actions

  April 1, 2011 at 9:05 am

  #1306548

  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]

• C.K.Shaiju

  SSCarpal Tunnel

  Points: 4228

  More actions

  April 1, 2011 at 9:08 am

  #1306553

  use PIVOT

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

• JackIntheBox

  SSCrazy

  Points: 2022

  More actions

  April 1, 2011 at 9:15 am

  #1306562

  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

• SpitFireXVII

  SSC Veteran

  Points: 212

  More actions

  April 1, 2011 at 9:26 am

  #1306570

  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. 🙂

• parthi-1705

  SSCrazy Eights

  Points: 9277

  More actions

  April 1, 2011 at 3:48 pm

  #1306705

  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