| Author |
 Topic  |
|
|
evets
Starting Member
3 Posts |
 Posted - 12/20/2012 : 08:45:24
|
Hello,
I must create a query usable through excel, pointing a sql 2005.
I have a similar situation:
CLIENTS
-------
ID
---
1
2
3
4
PROTOCOLS
------------------
IDCLIENT | NUMBERS
------------------
1 AA
1 BB
2 XX
2 YY
2 ZZ
I wish to obtain
Clients Protocols
---------------------------
1 AA, BB
2 XX, YY, ZZ
or, at least:
Clients Protocols Protocols2 PRotocols3 ...
-----------------------------------------------------------------
1 AA BB
2 XX YY ZZ ...
I Cannot forsee how many occurrencies of each record I will have, but I could set a Maximum (5 or 10 max).
Is there a way to obtain this ?
Thank you
Stefano |
Edited by - evets on 12/20/2012 11:08:35
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 12/20/2012 : 11:19:13
|
for former output use
SELECT Clients,
STUFF((SELECT ',' + NUMBER FROM PROTOCOLS WHERE IDCLIENTS = c.ID FOR XML PATH('')),1,1'') AS Protocols
FROM CLIENTS c
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
evets
Starting Member
3 Posts |
Posted - 12/20/2012 : 11:35:09
|
Thank you but I obtain this:
SELECT prpratiche,
STUFF((SELECT ',' + att_descrizione FROM agattivita
WHERE att_idpratica = c.pra_id FOR XML PATH('')),1,1'') AS Protocols
FROM prpratiche c
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ''.
thank you very much !
quote:
Originally posted by visakh16
for former output use
SELECT Clients,
STUFF((SELECT ',' + NUMBER FROM PROTOCOLS WHERE IDCLIENTS = c.ID FOR XML PATH('')),1,1'') AS Protocols
FROM CLIENTS c
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 12/20/2012 : 11:43:09
|
missed a comma
SELECT prpratiche,
STUFF((SELECT ',' + att_descrizione FROM agattivita
WHERE att_idpratica = c.pra_id FOR XML PATH('')),1,1,'') AS Protocols
FROM prpratiche c
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 12/20/2012 : 11:44:26
|
quote:
Originally posted by evets
Thank you but I obtain this:
SELECT prpratiche,
STUFF((SELECT ',' + att_descrizione FROM agattivita
WHERE att_idpratica = c.pra_id FOR XML PATH('')),1,1,'') AS Protocols
FROM prpratiche c
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ''.
thank you very much !
quote:
Originally posted by visakh16
for former output use
SELECT Clients,
STUFF((SELECT ',' + NUMBER FROM PROTOCOLS WHERE IDCLIENTS = c.ID FOR XML PATH('')),1,1'') AS Protocols
FROM CLIENTS c
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
sql query
