SQL: Two rows in one

• raphikdanish

  Newbie

  Points: 7

  More actions

  February 7, 2011 at 5:58 am

  #246379

  Hi,

  I have a table

  TableName: Index

  indID indDesc indNo

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

  1 string1 999

  2 string2 999

  3 string3 998

  4 string4 998

  I want to display all the records where indNo=999, but the output should be like below:

  indDesc indDesc indNo

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

  string1 string2 9999

  I will be very much thankful to receive reply.

  Thanks.

  Danish

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  February 7, 2011 at 7:14 am

  #1283097

  I think you should do such a transformation on the app side, not on the database side.

  That said, you could achieve it with some dynamic sql:

  IF OBJECT_ID('tempdb..#Index') IS NOT NULL

  DROP TABLE #Index

  CREATE TABLE #Index (

  indId int,

  indDesc varchar(50),

  indNo int

  )

  INSERT INTO #Index

  SELECT 1, 'string1', 999

  UNION ALL SELECT 2, 'string2', 999

  UNION ALL SELECT 3, 'string3', 998

  UNION ALL SELECT 4, 'string4', 998

  DECLARE @sql varchar(max)

  DECLARE @indNo int

  SET @indNo = 999

  -- Concatenate results in a single string

  SET @sql = STUFF((

  SELECT DISTINCT ', ' + QUOTENAME(indDesc ,'''') + ' AS ' + QUOTENAME(indDesc) AS [text()]

  FROM #Index

  WHERE indNo = @indNo

  ORDER BY 1

  FOR XML PATH('')

  )

  , 1, 1, SPACE(0));

  SET @sql = 'SELECT ' + @sql + ', ' + CAST(@indNo AS varchar(50)) + ' AS indNo'

  EXEC(@sql)

  Hope this helps

  Gianluca

  -- Gianluca Sartori

• Steve Jones - SSC Editor

  SSC Guru

  Points: 739010

  More actions

  February 7, 2011 at 7:46 am

  #1283121

  If you need more help, search for cross tab query or report. That's what you are doing. You can also look at the PIVOT command.