Return results horizontally

• George Agiovlasitis-260202

  SSC Rookie

  Points: 30

  More actions

  September 13, 2005 at 4:05 pm

  #165198

  Can anyone help with a query that will return results horizontally in SQL Server 2000?

  For example, the table contains the following:

   

  Name

  -----

  Smith

  Jones

  Rant

  Docks

  I'd like the result to be 'Smith|Jones|Rant|Docks'.

• sushila

  SSC-Dedicated

  Points: 35293

  More actions

  September 13, 2005 at 4:39 pm

  #589536

  DECLARE @singleRow varchar(1000)

  SET @singleRow = ''

  SELECT @singleRow = COALESCE(@singleRow + Name + '|', '')

  FROM myTable

  PRINT @singleRow

  
  
  
  
  
  
  **ASCII stupid question, get a stupid ANSI !!!**

• lindss01

  SSCrazy

  Points: 2264

  More actions

  September 14, 2005 at 3:17 pm

  #589938

  Perhaps another answer... ugly but it turns it around.

  Any other thoughts?

   

  if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_TABLE_COAL]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

  drop table [dbo].[T_TABLE_COAL]

  GO

  CREATE TABLE [dbo].[T_TABLE_COAL] (

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

   [TAB_TEXT] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

  ) ON [PRIMARY]

  GO

  ALTER TABLE [dbo].[T_TABLE_COAL] WITH NOCHECK ADD

   CONSTRAINT [PK_T_TABLE_COAL] PRIMARY KEY  CLUSTERED

   (

    [TAB_ID]

  &nbsp  ON [PRIMARY]   --That's a parenthesis

  GO

  INSERT INTO [ROSK_SWL].[dbo].[T_TABLE_COAL]([TAB_TEXT])

  VALUES('Smith')

  INSERT INTO [ROSK_SWL].[dbo].[T_TABLE_COAL]([TAB_TEXT])

  VALUES('Jones')

  INSERT INTO [ROSK_SWL].[dbo].[T_TABLE_COAL]([TAB_TEXT])

  VALUES('Rant')

  INSERT INTO [ROSK_SWL].[dbo].[T_TABLE_COAL]([TAB_TEXT])

  VALUES('Docks')

  SELECT [TAB_ID], [TAB_TEXT] FROM [ROSK_SWL].[dbo].[T_TABLE_COAL]

  DECLARE @singleRow varchar(1000)

  SET @singleRow = ''

  SELECT @singleRow = COALESCE(@singleRow + TAB_TEXT + '|', '')

  FROM [ROSK_SWL].[dbo].[T_TABLE_COAL]

  SELECT @singleRow

  PRINT @singleRow

  SELECT SMITH.[TAB_TEXT], JONES.[TAB_TEXT], RANT.[TAB_TEXT], Docks.[TAB_TEXT]

  FROM

  ((SELECT [TAB_TEXT],

   1 [ID]

  FROM [ROSK_SWL].[dbo].[T_TABLE_COAL]

  WHERE [TAB_TEXT] =  'Smith') SMITH left outer join

  (SELECT [TAB_TEXT],1 [ID]

  FROM [ROSK_SWL].[dbo].[T_TABLE_COAL]

  WHERE [TAB_TEXT] =  'JONES') JONES

   ON smith.[ID] = JONES.[ID] left outer join

  (SELECT [TAB_TEXT],1 [ID]

  FROM [ROSK_SWL].[dbo].[T_TABLE_COAL]

  WHERE [TAB_TEXT] =  'Rant') RANT

   ON smith.[ID] = Rant.[ID]left outer join

  (SELECT [TAB_TEXT],1 [ID]

  FROM [ROSK_SWL].[dbo].[T_TABLE_COAL]

  WHERE [TAB_TEXT] =  'Docks') Docks

   ON smith.[ID] = Docks.[ID])

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  September 14, 2005 at 3:47 pm

  #589946

  I don't think he meant cross-tab, but nice exemple .