| 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 BesluitnummerCL010001 5-3-08 aanvraag binnen bij t informatie.. 5CL010001 23-4-08 indicatie binnen op t AZR, VP.. 106CL010001 25-2-09 indicatie binnen op t.. 107CL010001 9-11-09 indicatie binnen op.. 108CL010002 26-9-07 aanvraag binnen op t AZR.. 1CL010002 19-12-07 indicatie binnen op t AZR.. 102CL010002 16-11-09 indicatie binne op AZR.. 103CL010003 18-3-09 LET OP: Dit blijkt… 1So I want the result to be:ClientnrQzorg Opmerkingen1 Opmerkingen2 Opmerkingen3 Opm4CL010001 5-3-8 aanvr. 23-4-08 indicat.. 25-2-09 indicatie.CL010002 26-09-07 a… 19-12-07 indic.. .. etcCL010003 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 EigOpmIndicatieEnClientnrQzorgAlleKaartenPIVOT( group by(ClientnrQzorg)FOR [Opmerkingen] IN ( [1], [2],[3],[4], [5],[6],[7], ,[9],[10], [11],[12])) order by ClientnrQzorgerror: Msg 156, Level 15, State 1, Line 11Incorrect 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 EigOpmIndicatieEnClientnrQzorgAlleKaartenPIVOT( MAX(ClientnrQzorg) FOR [Opmerkingen] IN ( [1],[2],[3],[4],[5],[6],[7],[[spoiler][/spoiler]8],[9],[10], [11],[12])) AS porder 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
|
| 2005GN |
|
|
|
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 6Incorrect 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 operatorexec 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 90GN |
|
|
|
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 80GN |
|
|
|
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 1Pivot 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