| Author |
Topic |
|
gnaus
Starting Member
41 Posts |
 Posted - 2011-11-30 : 04:08:33
|
Dear reader,
I have this table. I want to make colums from the rows.
Al “Opmerkingen” with the same “ClientnrQzorg” on ONE row, so multiple colums “Opmerkingen” (in stead of now, multiple rows per “ClientnrQzorg”).
ClientnrQzorg Opmerkingen Besluitnummer
CL010001 5-3-08 aanvraag binnen bij t informatie.. 5
CL010001 23-4-08 indicatie binnen op t AZR, VP.. 106
CL010001 25-2-09 indicatie binnen op t.. 107
CL010001 9-11-09 indicatie binnen op.. 108
CL010002 26-9-07 aanvraag binnen op t AZR.. 1
CL010002 19-12-07 indicatie binnen op t AZR.. 102
CL010002 16-11-09 indicatie binne op AZR.. 103
CL010003 18-3-09 LET OP: Dit blijkt… 1
So I want the result to be:
ClientnrQzorg Opmerkingen1 Opmerkingen2 Opmerkingen3 Opm4
CL010001 5-3-8 aanvr. 23-4-08 indicat.. 25-2-09 indicatie.
CL010002 26-09-07 a… 19-12-07 indic.. .. etc
CL010003 18-3-09 LET..
But also I want that it’s clear wich “Opmerkingen” belongs to wich “Besluitnummer”!!!
Thank you!
(ps I found something about Pivot’s etc. on this forum, but I couldn’t translate quite well to my situation)
GN |
|
|
gnaus
Starting Member
41 Posts |
Posted - 2011-11-30 : 05:12:10
|
I tried this:
select 'Opmerkingen'
[1],[2],[3],[4],[5],[6],[7], ,[9],[10],[11]
from EigOpmIndicatieEnClientnrQzorgAlleKaarten
PIVOT
(
group by(ClientnrQzorg)
FOR
[Opmerkingen]
IN ( [1], [2],[3],[4], [5],[6],[7], ,[9],[10], [11],[12])
)
order by ClientnrQzorg
error: Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'group'.
GN |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-11-30 : 06:10:19
|
[code]
select 'Opmerkingen'
[1],[2],[3],[4],[5],[6],[7],[[spoiler][/spoiler]8],[9],[10],[11]
from EigOpmIndicatieEnClientnrQzorgAlleKaarten
PIVOT
(
MAX(ClientnrQzorg)
FOR [Opmerkingen]
IN ( [1],[2],[3],[4],[5],[6],[7],[[spoiler][/spoiler]8],[9],[10], [11],[12])
) AS p
order by ClientnrQzorg[/code]
KH
[spoiler]Time is always against us[/spoiler]
|
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-11-30 : 06:11:28
|
are you using SQL 2000 or 2005 ?
KH
[spoiler]Time is always against us[/spoiler]
|
|
|
|
gnaus
Starting Member
41 Posts |
Posted - 2011-12-01 : 03:54:51
|
2005
GN |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-12-01 : 03:57:27
|
good. then the PIVOT Query i posted should work
KH
[spoiler]Time is always against us[/spoiler]
|
|
|
|
gnaus
Starting Member
41 Posts |
Posted - 2011-12-01 : 04:14:44
|
unfortunately I get the error: Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '('.
GN |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-12-01 : 04:19:06
|
what is your db compt level ? You need to be in 90 to use the PIVOT operator
exec sp_dbcmptlevel <database name>
KH
[spoiler]Time is always against us[/spoiler]
|
|
|
|
gnaus
Starting Member
41 Posts |
Posted - 2011-12-01 : 05:55:26
|
yes sql 2005 level 90
GN |
|
|
|
gnaus
Starting Member
41 Posts |
Posted - 2011-12-01 : 05:56:32
|
oh no sorry : The current compatibility level is 80.
GN |
|
|
|
gnaus
Starting Member
41 Posts |
Posted - 2011-12-01 : 06:07:47
|
datbase server is level 90 but database itself is 80
GN |
|
|
|
gnaus
Starting Member
41 Posts |
Posted - 2011-12-01 : 06:16:11
|
I will place the table in an other database where the level is 90 en try the same formula again. I'll let you know.
GN |
|
|
|
gnaus
Starting Member
41 Posts |
Posted - 2011-12-01 : 07:01:49
|
that works a little bit better but still not totally. result:
Msg 488, Level 16, State 1, Line 1
Pivot columns must be comparable. The type of column "Opmerkingen" is "text", which is not comparable.
GN |
|
|
|
gnaus
Starting Member
41 Posts |
|
|
|
From row to column