September 15, 2008 at 1:28 pm
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
September 15, 2008 at 1:43 pm
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
September 15, 2008 at 3:36 pm
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
September 16, 2008 at 8:03 am
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
September 16, 2008 at 8:21 pm
Thank you so much, it is working.
Regards
Vijji
September 18, 2008 at 9:01 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply