[MSSQL] regels naar kolommen

[bastv]

Ik probeer dmv sql pivot/unpivot tabel regels om te zetten naar kolommen.

brontabel = OrderOpties

OrderNr	OptieCode	OptieWaarde
123456	1	waarde1
123456	2	waarde2
123456	3	waarde3
123456	4	waarde4

(totaal 300 records per order)

nu wil ik 1 regel met daarin de waarde van optie 2 en 3.

OrderNr	Optie2	Optie3
123456	waarde2	waarde3

nu heb ik de volgende query die niet werkt:

SQL:

SELECT OrderNr,Optie2,Optie3
FROM OrderOpties
PIVOT (MAX(OptieWaarde) FOR [OptieCode] IN ([Optie2],[Optie3])) AS p
where OrderNr = '123456'

met dit als resultaat:

OrderNr	Optie2	Optie3
123456	NULL	NULL
123456	waarde2	NULL
123456	NULL	waarde3
123456	NULL	NULL

hoe kan ik hier 1 regel van maken?
distinct/group by etc geprobeerd maar kom er niet uit.

[RobIII]

Laat ik eerst eens vragen waarom je daar "1 regel" (1 record) van wil maken? Waarom laat je het niet aan je view/report over om hier iets van te bakken?

[bastv]

omdat dit voor een view nodig is met joins etc.
ook moet er via 1 query alle order info opgehaald worden (software van derden)

[aawe mwan]

Ik zou het ongeveer zo doen:

SQL:

SELECT DISTINCT
 C.OrderNr,
 (SELECT OptieWaarde FROM OrderOpties AS A WHERE A.OptieCode=2 AND A.OrderNr=C.OrderNr) AS Optie2,
 (SELECT OptieWaarde FROM OrderOpties AS B WHERE B.OptieCode=3 AND B.OrderNr=C.OrderNr) AS Optie3
FROM OrderOpties AS C;

Afhankelijk van je database performt zoiets nog best aardig.

[bastv]

bovenstaande query is een simpel voorbeeld.
OrderOpties heeft meer dan 300 records per order en ik wil er een stuk of 30 uitlezen, 30 subquerys vind ik niet echt een goede oplossing.

dit moet toch kunnen met pivot/unpivot

[aawe mwan]

bastv schreef op dinsdag 25 mei 2010 @ 16:54:
bovenstaande query is een simpel voorbeeld.

Dan denk ik dat jouw brontabel ook meer kolommen heeft dan de gegeven 3 kolommen.
Probeer eens of het helpt als je op de plaats van:

code:

FROM OrderOpties

een subquery zet waarmee je alleen de 3 kolommen selecteert uit de tabel OrderOpties die je in de pivot wilt gebruiken. Dat is ook hoe ze het hier doen.

[bastv]

super dat was de oplossing!

SQL:

SELECT OrderNr,Optie2,Optie3
FROM (
    SELECT OrderNr,OptieCode,OptieWaarde from OrderOpties 
    where OptieCode in ('Optie2','Optie3')
    ) pv
PIVOT (MAX(OptieWaarde) FOR [OptieCode] IN ([Optie2],[Optie3])) AS p
where OrderNr = '123456'

[Killemov]

Je hebt in dit geval toch helemaal geen pivot meer nodig? Als je uit een subquery kunt selecteren, wat je doet, ben je eigenlijk al klaar.

[bastv]

pivot heb je wel nodig om het simpel te houden
als je alles op 1 regel wil houden met subquerys moet je de query van aawe mwan in "[MSSQL] regels naar kolommen" gebruiken.
als je dit voor 30+ OrderOpties wilt doen heb je heel wat subquerys nodig wat naast dat het niet echt DRY is ook nog een flinke performance impact heeft.

[Killemov]

bastv schreef op woensdag 26 mei 2010 @ 10:36:
pivot heb je wel nodig om het simpel te houden
als je alles op 1 regel wil houden met subquerys moet je de query van aawe mwan in "[MSSQL] regels naar kolommen" gebruiken.
als je dit voor 30+ OrderOpties wilt doen heb je heel wat subquerys nodig wat naast dat het niet echt DRY is ook nog een flinke performance impact heeft.

Je hebt scheef gelezen of je begrijpt het niet. Je selecteert al uit een subquery ( FROM ( select ... ) ). Dat is echt iets heel anders dan subqueries in je selectie velden gebruiken. ( select ... ( select ...), (select ...) FROM ... )
Gewoon die ene subquery uitbreiden lijkt me genoeg. Maar goed, voor jou (mssql) werkt de pivot ook.