How can I apply STUFF () for comma
I read this: http://blog.sqlauthority.com/2012/09/14/sql-server-grouping-by-multiple-columns-to-single-column-as-a-string/
And I can apply this to one of my requests:
SELECT t.TicketID, STUFF(
(SELECT ',' + tt.Tag
FROM TicketTag tt
WHERE tt.TicketID = t.TicketID
FOR XML PATH('')),1,1,'') AS CSV
FROM Ticket AS t
GROUP BY t.TicketID
GO
This leads to the following:
TicketID CSV
1 tsql, sqlserver, c++
2 hi, bye, no
Now it worked because I was able to directly join one table ... Now I want STUFF()
to reapply to display the TicketID with whoever is assigned to it.
The request that shows the person to whom the ticket is assigned looks like this:
SELECT l.Login
FROM Ticket t1
LEFT JOIN
TicketAssignments tass
ON
tass.TicketID=t1.TicketID
LEFT JOIN
Login l
ON
l.LoginID = tass.LoginID
However, I am missing two key things:
1) I need to display the TicketID like in my first example (with a group by ticketID) 2) I need STUFF () the login name so that it goes beyond the comma.
Please stick with STUFF () I know this may not be the best way at times, but I'm just trying to use it in my code.
change
3 tables
Ticket
------
TicketID
TicketAssignments
-----------------
TicketID
LoginID
Login
------
LoginID
Sample data:
Ticket
------
1
2
3
TicketAssignments
------------------
1 25
1 26
2 25
3 26
3 27
Login
-----
25 Joe
26 Jon
27 Jason
Result of what I want:
TicketID Assignment
--------------------
1, "Joe, Jon"
2, "Joe"
3, "Jon", "Jason"
That is, Joe and John got ticket 1 Joe was assigned ticket 2 John and Jason were assigned ticket 3
So STUFF () just concatenates them into one line for each ticket. I know this is not efficient, I am not asking for optimization right now ...
As mentioned, I have to get the ticket job:
SELECT l.Login FROM Ticket t1 LEFT TicketAssignments tass AT tass.TicketID = t1.TicketID LEFT Login l AT l.LoginID = tass.LoginID
However, I am missing two key things: 1) I need to display the TicketID as I did in my first example (with a group by ticketID) 2) I need STUFF () the login name to get it separated by comma
source to share
SELECT t.TicketID, Assignment = STUFF(
(
SELECT ', ' + l.Name
FROM dbo.Login AS l
INNER JOIN dbo.TicketAssignments AS ta
ON l.LoginID = ta.LoginID
WHERE ta.TicketID = t.TicketID
FOR XML PATH(''), TYPE
).value('.[1]','nvarchar(max)'), 1, 2, '')
FROM dbo.Ticket AS t
ORDER BY t.TicketID;
source to share
STUFF
just removes the leading comma that comes from the way you concatenate the tags (or login.name as you ask). The clause FOR XML
actually does the work of concatenation.
See this example:
declare @Ticket table (TicketId int);
insert into @Ticket
select 1 union all select 2 union all select 3;
declare @TicketAssignments table (TicketId int, LoginId int)
insert into @TicketAssignments
select 1,25 union all
select 1,26 union all
select 2,25 union all
select 3,26 union all
select 3,27;
declare @Login table (LoginId int, Name varchar(10));
insert into @Login
select 25, 'Joe' union all
select 26, 'Jon' union all
select 27, 'Jason';
select ticketId,
p.n,
stuff(p.n, 1, 1, '')
from @Ticket t
cross
apply ( select ',' + l.Name
from @TicketAssignments ta
join @Login l on ta.LoginId = l.LoginId
where ta.TicketId = t.TicketId
for xml path('')
)p(n)
source to share
The following example shows how to apply the stuff function on two columns. I have 3 tables in hierarchical order. Job-> storejob-> StorejobAssignment. Tables in a hierarchy are associated with a foreign key. The primary key of the Job table, which is JobNo_pk, will appear as a foreign key in the store's jobs table as Jobno_fk. But JObno_fk is not available in the StoreJobAssignment table because it is in hierarchical order.
Select Top 10 j.JobNo_pk,
Stuff((select ','+ convert(varchar,StoreJobNo_pk) from Storejob
where j.JobNo_PK=StoreJob.JobNo_FK for XML path ('') ),1,1,'') as StorejobNO_pk,
Stuff((select ','+ convert(varchar,StoreJobAssignmentNo_pk) from StorejobAssignment inner join StoreJob on StoreJob.StoreJobNo_PK=StoreJobAssignment.StoreJobNo_FK
where StoreJob.Storejobno_pk=StoreJobAssignment.StorejobNo_fk for XML path ('') ),1,1,'') as Storejobassignmnet_pk
from job j
inner join storejob on j.jobno_pk=StoreJob.jobno_fk
inner join StorejobAssignment on StoreJob.StoreJobno_pk=StoreJobAssignment.Storejobno_fk
group by Jobno_pk
source to share