Author |
Topic |
jchoudja
Starting Member
41 Posts |
Posted - 2013-02-13 : 11:05:54
|
Hi, How can I select and display Multiple possible values of the same row in single row seperated by coma?exemple:table name: agrementID | type | status-----------------------------1 | 1 month | active1 | cession | active1 | day | inactive2 | 1 month | active2 | day | inactiveI want to display only active membership (status) for each ID in 1 row separated by coma. Note: there is no limitation on the numeber type for single IDID | mebership(s)--------------------------1 | 1 month, cession2 | 1 month Thank youjc |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-13 : 11:25:03
|
[code]SELECT ID,STUFF((select ',' + type from agrement where status = 'active' and id = t.id for xml path('')),1,1,'')FROM(SELECT DISTINCT ID FROM agrement)t[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
jchoudja
Starting Member
41 Posts |
Posted - 2013-02-13 : 11:35:00
|
Thank you for your verry fast answer. But It I didn't express clearly what I wanted. I want to show only the Membership(s) field not the ID. This is just a part of my table.Membership(s)1 month, cession1 monthCan you please breack it down?jc |
|
|
jchoudja
Starting Member
41 Posts |
Posted - 2013-02-13 : 11:42:16
|
My table already contains many columns and mebership(s)is showing there in multiple rows when ID has mor than 1 type. All I want is to show all multiples Type for each ID in one row.jc |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-13 : 11:46:28
|
[code]SELECT STUFF((select ',' + type from agrement where status = 'active' and id = t.id for xml path('')),1,1,'') AS membershipsFROM(SELECT DISTINCT ID FROM agrement)t[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
jchoudja
Starting Member
41 Posts |
Posted - 2013-02-14 : 08:32:16
|
Thank you.I am Sorry but I still can do it on my onwne. I forgot to mantion data are from many tables linked togather. Here is the actual situation.TablesMembers (MemberID,MembershipID, MemberName)Membership (MembershipID, AgreementID, MebershipName)Agreement (AgrementID, AgreementName, AgreementIsActive(Yes or No))Tables are linked with: MembershipID and AgreementID and My SQL result should look like this:List all AgreementName for each MemberID seperated by comaMeberID | MemberName | Agreements----------------------------------------1.......|_John.......|student, cession2.......|_Meli.......|student 3.......|_Jean.......|1month, special, cession4.......|_Chad.......|specialNote. ignore Dot(.) this is just for table formatingThank youjc |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-14 : 09:46:15
|
its still the sameSELECT m.MemberID,m.MemberName,STUFF((SELECT ',' + AgreementName FROM Agreement WHERE AgrementID = ms.AgreementID AND AgreementIsActive='Yes' FOR XML PATH('')),1,1,'') AS AgreementsFROM membership msINNER JOIN members mON m.MembershipID = ms.membershipID ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
jchoudja
Starting Member
41 Posts |
Posted - 2013-02-14 : 10:14:52
|
Thank You. What does ms and m represent? Is ms used like dbo.AgreementID?jc |
|
|
jchoudja
Starting Member
41 Posts |
Posted - 2013-02-14 : 10:18:51
|
in my query, I use dbo.Agreement.AgreementName to specify AgrementName in Table Agreement. Should I then say like. STUFF((SELECT ',' + dbo.AgreementName FROM Agreement ..... or hust STUFF((SELECT ',' + AgreementName FROM Agreementjc |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-14 : 10:19:13
|
ms m are aliases ie short names for table------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
jchoudja
Starting Member
41 Posts |
Posted - 2013-02-14 : 12:00:29
|
Here is what I did: ....STUFF((SELECT ',' + dbo.Agreement.AgreementName FROM dbo.Agreement WHERE dbo.Agreement.AgreementID = dbo.Membership.AgreementID FOR XML PATH('')),1,1,'') AS Agreements....Just to collect all agreements active and noBut I am Getting Each agreement on its own line on Agreements columnWhat migh be wrong here?jc |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-14 : 12:03:13
|
show the FROM part. without which I cant understand where you're going wrongAlso whats was the issue with my query? why did you change it?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
jchoudja
Starting Member
41 Posts |
Posted - 2013-02-14 : 12:44:29
|
I see what is going wrong. Each AgreementID is unique so memeberID=1 will get 2 differents AgreementID for his 2 differents Agreement.In My table, only the memberID is duplicated and should be used to group all Agreements from the same member. How can I overcome this? Here it is as it:SELECT DISTINCT dbo.Member.MemberGUID, dbo.Agreement.AgreementGUID, dbo.MembershipAgreement.MembershipGUID, dbo.Membership.MemberGUID AS Expr1,STUFF((SELECT ',' + dbo.Agreement.AgreementName FROM dbo.Agreement WHERE dbo.Agreement.AgreementGUID = dbo.MembershipAgreement.AgreementGUID FOR XML PATH('')),1,1,'') AS AgreementsFROM dbo.Agreement INNER JOIN dbo.MembershipAgreement ON dbo.Agreement.AgreementGUID = dbo.MembershipAgreement.AgreementGUID INNER JOIN dbo.Membership ON dbo.MembershipAgreement.MembershipGUID = dbo.Membership.MembershipGUID INNER JOIN dbo.Member ON dbo.Membership.MemberGUID = dbo.Member.MemberGUIDjc |
|
|
jchoudja
Starting Member
41 Posts |
Posted - 2013-02-14 : 12:45:52
|
There are 2 tables between Member and Agreement.jc |
|
|
jchoudja
Starting Member
41 Posts |
Posted - 2013-02-14 : 12:52:49
|
Here is the relation chain between tablesMember.MemberGUID -> Membership.MemberGUID Membership.MembershipGUID -> MembershipAgreement.MembershipGUID MembershipAgreement.AgreementGUID -> Agreement.AgreementGUIDOnly MemberGUID is duplicated if the member has many Mebership but MembershipGUID and AgreementGUID are unique on each row.jc |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
jchoudja
Starting Member
41 Posts |
Posted - 2013-02-15 : 07:25:38
|
Thank you for your consideration. Here is sample of what I want.I have those 4 tables.MemberMemberGUID|MemberName----------------------1_________|_John_____2_________|_Celia____3_________|_Lucas____4_________|_Mehli____5_________|_Lucas____MembershipMembershipGUID|MemberGUID-------------------------M1____________|_1_____M2____________|_1____M3____________|_2____M4____________|_2____M5____________|_2____M6____________|_3____M7____________|_4____M8____________|_5____MebershipAgreementMebershipAgreementGUID|MembershipGUID|AgreementGUID---------------------------------------------------MS1___________________|_M1___________|_A1MS2___________________|_M2___________|_A2MS3___________________|_M3___________|_A3MS4___________________|_M4___________|_A4MS5___________________|_M5___________|_A5MS6___________________|_M6___________|_A6MS7___________________|_M7___________|_A7MS8___________________|_M8___________|_A8AgreementAgreementGUID|Agreement Name-----------------------------A1___________|__Mtm_____A2___________|__cession_____A3___________|__Mtm_____A4___________|__single_____A5___________|__double_____A6___________|__single_____A7___________|__cession_____A8___________|__single_____Here is the result I am looking for:MemberGUID|MemberName|Agrements----------------------------------------------1_________|_John_____|_Mtm, cession2_________|_Celia____|_Mtm, single, double3_________|_Lucas____|_single4_________|_Mehli____|_cession5_________|_Lucas____|_singleI have done this:SELECT DISTINCT dbo.Member.MemberGUID, dbo.Agreement.AgreementGUID, dbo.MembershipAgreement.MembershipGUID, dbo.Membership.MemberGUID AS Expr1,STUFF((SELECT ',' + dbo.Agreement.AgreementName FROM dbo.Agreement WHERE dbo.Agreement.AgreementGUID = dbo.MembershipAgreement.AgreementGUID FOR XML PATH('')),1,1,'') AS AgreementsFROM dbo.Agreement INNER JOINdbo.MembershipAgreement ON dbo.Agreement.AgreementGUID = dbo.MembershipAgreement.AgreementGUID INNER JOINdbo.Membership ON dbo.MembershipAgreement.MembershipGUID = dbo.Membership.MembershipGUID INNER JOINdbo.Member ON dbo.Membership.MemberGUID = dbo.Member.MemberGUIDBut I aml getting 8 rows instate of 5 so each Agreement is showing on its own row. I want to concatenate all agreements for the same member in one row seperated by coma.Thank you for any help.jc |
|
|
jchoudja
Starting Member
41 Posts |
Posted - 2013-02-15 : 09:31:41
|
Any help please?jc |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-15 : 14:12:08
|
[code]SELECT m.*,STUFF((SELECT ',' + a.[Agreement Name]FROM Membership mrJOIN MebershipAgreement maON ma.MembershipGUID = mr.MembershipGUIDJOIN Agreement aON a.AgreementGUID = ma.AgreementGUIDWHERE MemberGUID = m.MemberGUIDORDER BY a.AgreementGUIDFOR XML PATH('')),1,1,'') AS AgrementsFROM Member m[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
jchoudja
Starting Member
41 Posts |
Posted - 2013-02-15 : 15:11:07
|
Thanks a lot for your help and disponibility. I will try that when I get home. Thanks againjc |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-16 : 02:29:06
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Next Page
|