How to Convert rows into one column

Question

How to Convert rows into one column

Vijji-429978

SSC Eights!

Points: 983
More actions
September 15, 2008 at 1:28 pm

#192765

Hi every one
I have table which has values like this
PT_Window_IDPT_UD_KeyPT_UD_NumberSTRGA255
SOPENTRYL100011 4 MANUFACTURER
SOPENTRYL100011 6 SHIP
SOPENTRYL100011 8 PACK
SOPENTRYL100011 14 VESSEL
I want to display like this
PT_Window_IDPT_UD_Key4 6814
SOPENTRYL100011 MANUFACTURERSHIPPACKVESSEL
Please help me how to write query.
Thank you,
Vijji

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

You can use the Pivot function for that.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Vijji-429978 SSC Eights! Points: 983 More actions · Answer 2

Thank you for the suggestion

I tried using PIVOT, but rows are repeating.

SELECT PT_Window_ID, PT_UD_Key, [4] as Manufacturer, [6] as SHIPTERM

FROM EXT00101

PIVOT

(max(STRGA255) FOR PT_UD_Number in ([4], [6]))

AS pvt

WHERE PT_Window_ID = 'SOPENTRY' AND PT_UD_Key = 'L100011'

Thank you

Vijji

GSquared SSC Guru Points: 260824 More actions · Answer 3

I just tested this:

create table #T (

PT_Window_ID varchar(50),

PT_UD_Key varchar(50),

PT_UD_Number int,

STRGA255 varchar(50))

insert into #T

select 'SOPENTRY','L100011',4,'MANUFACTURER' union all

select 'SOPENTRY','L100011',6,'SHIP' union all

select 'SOPENTRY','L100011',8,'PACK' union all

select 'SOPENTRY','L100011',14,'VESSEL'

SELECT PT_Window_ID, PT_UD_Key, [4], [6], [8], [14]

FROM #T

PIVOT

(max(STRGA255) FOR PT_UD_Number in ([4], [6], [8], [14]))

AS pvt

WHERE PT_Window_ID = 'SOPENTRY' AND PT_UD_Key = 'L100011'

It seems to give what you asked for in the first post. I would need to have some sample data that produces duplicate rows in order to work with that. Can you post an insert statement that, when queried, gives duplicates?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Vijji-429978 SSC Eights! Points: 983 More actions · Answer 4

Vijji-429978

SSC Eights!

Points: 983

September 16, 2008 at 8:21 pm

#872775

Thank you so much, it is working.

Regards

Vijji

GSquared SSC Guru Points: 260824 More actions · Answer 5

You're welcome. Glad I could help.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon