Select Query Help!

• fuleye

  SSC Rookie

  Points: 30

  More actions

  March 18, 2008 at 1:00 am

  #379246

  hie all,

  I need a select query for Sql 2000 and also for sql 2005

  find script :

  Create Table #Temp

  (

  Sname varchar(20),

  Sdate Datetime

  )

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

  insert into #Temp

  Select 'Add1', '01/01/2007'

  union all

  Select 'Add2', '01/01/2007'

  union all

  Select 'Add3', '02/02/2007'

  union all

  Select 'Add4', '03/03/2007'

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

  select * from #Temp

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

  Sname Sdate

  ====== ======

  Add12007-01-01 00:00:00.000

  Add22007-01-01 00:00:00.000

  Add32007-02-02 00:00:00.000

  Add42007-03-03 00:00:00.000

  ===============================

  Now I need a Output Like this:

  2007-01-01 2007-02-02 2007-03-03

  ========== ========== ==========

  Add1 Add3 Add4

  Add2

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

  thanks in advance

  "Mo Suna Lo"

  "If you Dream it, You can Do it......."

• IN_Sandeep

  SSCertifiable

  Points: 6259

  More actions

  March 18, 2008 at 6:56 am

  #790772

  hey mo Suna Lo,

  try this..

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

  drop procedure [dbo].[crosstab]

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  SET ANSI_NULLS ON

  GO

  CREATE PROCEDURE crosstab

  @select varchar(8000),

  @sumfunc varchar(100),

  @pivot varchar(100),

  @table varchar(100)

  AS

  DECLARE @sql varchar(8000), @delim varchar(1)

  SET NOCOUNT ON

  SET ANSI_WARNINGS OFF

  EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')

  EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE '

  + @pivot + ' Is Not Null')

  SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

  SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )

  WHEN 0 THEN '' ELSE '''' END

  FROM tempdb.information_schema.columns

  WHERE table_name='##pivot' AND column_name='pivot'

  SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +

  stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '

  + @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot

  DROP TABLE ##pivot

  SELECT @sql=left(@sql, len(@sql)-1)

  SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')

  EXEC (@select)

  SET ANSI_WARNINGS ON

  /*

  EXECUTE crosstab 'SELECT Sname FROM Temp GROUP BY Sname', 'MAX(Sname)', 'SDate', 'Temp'

  */

  GO

  SET QUOTED_IDENTIFIER OFF

  GO

  SET ANSI_NULLS ON

  GO

  will it helpful?

  Cheers!

  Sandy.

  --