sql query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sql query

Author

Topic

evets
Starting Member

3 Posts

Posted - 2012-12-20 : 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

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-20 : 10:49:48

Check the bottom of this post on how to pivot dynamic

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=181589

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-20 : 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 - 2012-12-20 : 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

52326 Posts

Posted - 2012-12-20 : 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
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-20 : 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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources