Can I use UNPIVOT in this case?

• nguyennd

  SSCommitted

  Points: 1881

  More actions

  January 15, 2010 at 1:17 am

  #215433

  I've a problem:-D. I need result same as above, but i can't write it in one SQL. Please help me.

  DECLARE @DATA TABLE

  (

  INT NOT NULL,

  FIELDA INT NOT NULL,

  FIELDB INT NOT NULL,

  FIELDC INT NOT NULL,

  FIELDD INT NOT NULL,

  FIELDE INT NOT NULL

  );

  INSERT INTO @DATA (,FIELDA,FIELDB,FIELDC,FIELDD,FIELDE)

  VALUES

  (1,1,2,3,4,5),

  (2,11,22,33,44,55),

  (3,111,222,333,444,555)

  RESULT

  KEY FIELDA FIELDB FIELDC FIELDD FIELDE

  1 1 2 3

  1 4 5

  2 11 22 33

  2 44 55

  3 111 222 333

  3 444 555

  http://unitech.vn

• Dave Ballantyne

  SSC-Dedicated

  Points: 33556

  More actions

  January 15, 2010 at 1:32 am

  #1104084

  Something like this ?

  ;with cteRows

  as

  (

  select 1 as r

  union all

  select 2

  )

  Select ,

  case when r =1 then convert(char(10),Fielda) else '' end,

  case when r =1 then convert(char(10),Fieldb) else '' end,

  case when r =1 then convert(char(10),Fieldc) else '' end,

  case when r =2 then convert(char(10),Fieldd) else '' end,

  case when r =2 then convert(char(10),Fielde )else '' end

  from @data

  cross join cteRows

  order by ,r

  ---

  
  Clear Sky SQL
  My Blog[/url]

• nguyennd

  SSCommitted

  Points: 1881

  More actions

  January 15, 2010 at 3:08 am

  #1104102

  Thanks for your saving me.:-D

  http://unitech.vn

• Jeff Moden

  SSC Guru

  Points: 1000539

  More actions

  January 15, 2010 at 8:32 pm

  #1104546

  nguyennd (1/15/2010)

  ---

  I've a problem:-D. I need result same as above, but i can't write it in one SQL. Please help me.

  Why did it have to be written in "one SQL"?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
  "Change is inevitable... change for the better is not".
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  Intro to Tally Tables and Functions

• ChrisM@home

  SSC-Insane

  Points: 24260

  More actions

  January 16, 2010 at 2:51 am

  #1104571

  Could you use unpivot for this? I'm sure you could, if you were to pivot the data first. You don't need to, it's a very simple requirement:

  SELECT *

  FROM (

  SELECT , FIELDA, FIELDB, FIELDC, CAST(NULL AS INT) AS FIELDD, CAST(NULL AS INT) AS FIELDE

  FROM @DATA

  UNION ALL

  SELECT , CAST(NULL AS INT) AS FIELDA, CAST(NULL AS INT) AS FIELDB, FIELDC, FIELDD, FIELDE

  FROM @DATA

  ) d ORDER BY , FIELDA desc

  ---

  [font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

  For better assistance in answering your questions, please read this[/url].

  ---

  Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

  Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]