horizontalTo vertical Columns

• parthi-1705

  SSCrazy Eights

  Points: 9277

  More actions

  November 10, 2009 at 6:13 am

  #206439

  SET NOCOUNT ON

  DECLARE @Temp TABLE(Value varchar(100))

  INSERT INTO @Temp SELECT 1

  INSERT INTO @Temp SELECT 2

  INSERT INTO @Temp SELECT 3

  INSERT INTO @Temp SELECT 4

  INSERT INTO @Temp SELECT 5

  INSERT INTO @Temp SELECT 6

  Select * From @Temp

  o/p:

  1

  2

  3

  4

  5

  6

  DECLARE @collection VARCHAR(8000) ,@op VARCHAR(200)

  select @collection=coalesce(@Collection+',','')+Value from @Temp

  select @collection

  it is coming but

  i need the data like this:

  Select 1,2,3,4,5,6

  here only 6 datas are there but it may increase upto 366 or reduce it has to loaded dynamically.

  Only one column in that table.If i give input as 10 it should return

  Select 1,2,3,4,5,6,7,8,9,10

  I am trying for report (1,2,... are days )

  i have to show 1,2,3,....365 in a single sheet no probs about the length

  how to get

  Thanks in advance

  Parthi

  Thanks
  Parthi

• LutzM

  SSC Guru

  Points: 107049

  More actions

  November 10, 2009 at 11:58 am

  #1077426

  Usually, a ColToRowConversion of this kind is done using the FOR XML clause:

  SELECT stuff((SELECT ', ' + Value From @Temp FOR XML path('')),1,2,'')

  Using your sample data it will result in 1, 2, 3, 4, 5, 6

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• parthi-1705

  SSCrazy Eights

  Points: 9277

  More actions

  November 10, 2009 at 10:12 pm

  #1077637

  Hi Lutz

  Thanks for your replay actually it is done by below

  DECLARE @collection VARCHAR(8000)

  select @collection=coalesce(@Collection+',','')+Value from @Temp

  select @collection (No column name)

  but i dont want this one i need

  Select 1(No column name),2(No column name),3(No column name),4(No column name).....

  Thanks

  Parthi

  Thanks
  Parthi

• LutzM

  SSC Guru

  Points: 107049

  More actions

  November 11, 2009 at 3:36 am

  #1077705

  The only difference I can see is the word "SELECT" aat the beginning of the output...

  SELECT 'Select '+ stuff((SELECT ', ' + Value From @Temp FOR XML path('')),1,2,'')

  Based on your original requirement

  If i give input as 10 it should return

  Select 1,2,3,4,5,6,7,8,9,10

  The output from the code above will be exactly as requested.

  If that's not the result you're expecting you might want to define your requirement a little more in detail.

  Since you mentioned column names: It it possible you're asking for dynamic SQL to query a table with flexible number of columns?

  If so, please follow the first link in my signature on how to provide data to get fast and tested answers.

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• parthi-1705

  SSCrazy Eights

  Points: 9277

  More actions

  November 11, 2009 at 5:21 am

  #1077729

  Hi

  I have attached the screen shot my Requ.

  Thanks

  Parthi

  Thanks
  Parthi

• LutzM

  SSC Guru

  Points: 107049

  More actions

  November 11, 2009 at 9:36 am

  #1077887

  Ok, now it makes sense...

  Would have helped a lot if the statement

  SELECT 1,2,3,4,5,6

  had SQL tags around (maybe even with expected output) to look like

  SELECT 1,2,3,4,5,6

  /* result set

  (No column name)(No column name)(No column name)(No column name)(No column name)(No column name)

  123456 */

  This would have saved a few hours...

  Anyhow, here's the dynamic SQL I think you need (based on your sample data):

  -- make the data available for dynamic SQL (cannot use table variable)

  Select * INTO #temp From @Temp

  DECLARE @sql nvarchar(max)

  -- build the dynamic select statement

  SELECT @sql = coalesce(@sql,'SELECT ') + 'MAX(CASE WHEN Value='+cast(Value as char(2))+' THEN Value ELSE '''' END),'

  FROM #temp a GROUP BY Value

  ORDER BY Value

  -- remove the trailing comma

  SET @sql = reverse(stuff(reverse(@sql),1,1,''))

  -- complete the dynamic string

  SET @sql = @sql + '

  FROM #temp '

  PRINT @sql -- For Debugging

  --EXEC sp_executesql @sql

  /* result set

  (No column name)(No column name)(No column name)(No column name)(No column name)(No column name)

  123456

  */

  -- cleanup

  DROP TABLE #temp

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• parthi-1705

  SSCrazy Eights

  Points: 9277

  More actions

  November 11, 2009 at 10:47 pm

  #1078170

  Hi lutz

  This is what exactly i need thanks for your help,it saved my lot of my time.

  Thanks

  Parthi

  Thanks
  Parthi

• LutzM

  SSC Guru

  Points: 107049

  More actions

  November 12, 2009 at 12:43 am

  #1078189

  Glad I could help and sorry it took that long to figure out what you've been looking for...

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• parthi-1705

  SSCrazy Eights

  Points: 9277

  More actions

  November 12, 2009 at 2:38 am

  #1078211

  "More the Wait Better the you got" i got better answer so no problem

  Thanks
  Parthi