Script to Reformat Results to Display Horizontally

• Jesse McLain

  SSCommitted

  Points: 1782

  More actions

  July 29, 2009 at 9:23 am

  #73307

  Does anyone have a script that will output the results of a SELECT statement "horizontally"? For example, let's say that the result set has columns Col1, Col2, and Col3, and two rows. The output I want is (formatting corrected 1:07 PM 7/29/2009):

  {Col1_Name}":"{Row1_Col1_Value}

  {Col2_Name}":"{Row1_Col2_Value}

  {Col3_Name}":"{Row1_Col3_Value}

  {Col1_Name}":"{Row2_Col1_Value}

  {Col2_Name}":"{Row2_Col2_Value}

  {Col3_Name}":"{Row2_Col3_Value}

  I can write something for it, but time is pressing.

  TIA,

  Jesse

  Jesse McLain
  jesse@jessemclain.com
  www.jessemclain.com
  www.jessesql.blogspot.com

• Lowell

  SSC Guru

  Points: 323516

  More actions

  July 29, 2009 at 10:59 am

  #1031894

  here's an example which uses the FORXML to produce a comma delimited list as a result for each ID:

  is this what you were after:

  Resource_Id    Skills
          101    oracle,sqlserver,vb.net
          102    excel,java,sqlserver
          103    java,oracle,vb.net
  

  your original post had html tags , which the forum strips out; all we saw were the colons.

  declare @skills table (Resource_Id int, Skill_Id varchar(20))

  insert into @skills

  select 101, 'sqlserver' union all

  select 101, 'vb.net' union all

  select 101, 'oracle' union all

  select 102, 'sqlserver' union all

  select 102, 'java' union all

  select 102, 'excel' union all

  select 103, 'vb.net' union all

  select 103, 'java' union all

  select 103, 'oracle'

  ---

  select * from @skills s1

  --- Concatenated Format

  set statistics time on;

  SELECT Resource_Id,stuff(( SELECT ',' + Skill_Id

  FROM @skills s2

  WHERE s2.Resource_Id= s1.resource_ID --- must match GROUP BY below

  ORDER BY Skill_Id

  FOR XML PATH('')

  ),1,1,'') as [Skills]

  FROM @skills s1

  GROUP BY s1.Resource_Id --- without GROUP BY multiple rows are returned

  ORDER BY s1.Resource_Id

  set statistics time off;

  Jesse McLain (7/29/2009)

  ---

  Does anyone have a script that will output the results of a SELECT statement "horizontally"? For example, let's say that the result set has columns Col1, Col2, and Col3, and two rows. The output I want is:

  {Col1_Name}":"{Row1_Col1_Value}

  {Col2_Name}":"{Row1_Col2_Value}

  {Col3_Name}":"{Row1_Col3_Value}

  {Col1_Name}":"{Row2_Col1_Value}

  {Col2_Name}":"{Row2_Col2_Value}

  {Col3_Name}":"{Row2_Col3_Value}

  I can write something for it, but time is pressing.

  TIA,

  Jesse

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!