Can I use UNPIVOT in this case?

Question

Post reply

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
KEYFIELDAFIELDBFIELDCFIELDDFIELDE
1123
145
2112233
24455
3111222333
3444555
http://unitech.vn

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

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 · Answer 2

nguyennd

SSCommitted

Points: 1881

January 15, 2010 at 3:08 am

#1104102

Thanks for your saving me.:-D

http://unitech.vn

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

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)

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 4

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]