September 4, 2014 at 3:19 am
Hi All,
Scenario is like that single dept can have multiple LocationHeads, If Location heads are multiple then they should display in single column using *starting the name as mentioned bottom under required output. Please help.
Below is sample of data:
create table #Temp(depID int, Name varchar(50),LocationHead varchar(50))
insert into #temp values(1,'test','head1')
insert into #temp values(1,'test','head2')
insert into #temp values(1,'test','head3')
insert into #temp values(2,'test1','head1')
insert into #temp values(2,'test1','head2')
required output
depID Name LocationHead
1test *head1,*head2,*head3
2test1 *head1,*head2
Thanks,
Abhas.
September 4, 2014 at 5:33 am
abhas (9/4/2014)
Hi All,Scenario is like that single dept can have multiple LocationHeads, If Location heads are multiple then they should display in single column using *starting the name as mentioned bottom under required output. Please help.
Below is sample of data:
create table #Temp(depID int, Name varchar(50),LocationHead varchar(50))
insert into #temp values(1,'test','head1')
insert into #temp values(1,'test','head2')
insert into #temp values(1,'test','head3')
insert into #temp values(2,'test1','head1')
insert into #temp values(2,'test1','head2')
required output
depID Name LocationHead
1test *head1,*head2,*head3
2test1 *head1,*head2
Thanks,
Abhas.
Thanks for the sample data, it makes it much easier to help.
SELECT depID, Name, LocationHead
FROM (SELECT DISTINCT depID, Name
FROM #temp
)a
OUTER APPLY (SELECT STUFF((SELECT ',*'+LocationHead
FROM #temp
WHERE a.depID = depID
FOR XML PATH(''), TYPE
).value('.','NVARCHAR(MAX)'),1,1,'')
)oa([LocationHead]);
Produces: -
depID Name LocationHead
----------- -------------------------------------------------- -----------------------
1 test *head1,*head2,*head3
2 test1 *head1,*head2
If all you're interested in is the answer, you don't need to read the rest of this.
Let's take a quick look at what we're doing here, in an effort to explain what's going on.
Execute this: -
IF object_id('tempdb..#temp') IS NOT NULL
BEGIN;
DROP TABLE #temp;
END;
CREATE TABLE #temp
(
depID INT,
Name VARCHAR(50),
LocationHead VARCHAR(50)
);
INSERT INTO #temp
VALUES ( 1, 'test', 'head1' );
INSERT INTO #temp
VALUES ( 1, 'test', 'head2' );
INSERT INTO #temp
VALUES ( 1, 'test', 'head3' );
INSERT INTO #temp
VALUES ( 2, 'test1', 'head1' );
INSERT INTO #temp
VALUES ( 2, 'test1', 'head2' );
SELECT depID, ',*'+LocationHead
FROM #temp
FOR XML PATH(''), TYPE;
You should get back this: -
<depID>1</depID>,*head1<depID>1</depID>,*head2<depID>1</depID>,*head3<depID>2</depID>,*head1<depID>2</depID>,*head2
Now change your SELECT to this: -
SELECT ',*'+LocationHead
FROM #temp
FOR XML PATH(''), TYPE;
You'll get back something like this: -
,*head1,*head2,*head3,*head1,*head2
So, that has concatenated all of your "LoactionHead" data together. Now, we don't want that comma at the front of the string, so we use STUFF to get rid of it.
SELECT STUFF((SELECT ',*'+LocationHead
FROM #temp
FOR XML PATH(''), TYPE
).value('.','NVARCHAR(MAX)'),1,1,'');
So that now returns: -
*head1,*head2,*head3,*head1,*head2
Obviously this isn't quite what we're after, since it shows all of the depId information together. We split this with APPLY.
SELECT a.depID, a.Name, oa.[LocationHead]
FROM #temp a
OUTER APPLY (SELECT STUFF((SELECT ',*'+LocationHead
FROM #temp
FOR XML PATH(''), TYPE
).value('.','NVARCHAR(MAX)'),1,1,'')
)oa([LocationHead]);
That returns: -
depID Name LocationHead
----------- -------------------------------------------------- ------------------------------------
1 test *head1,*head2,*head3,*head1,*head2
1 test *head1,*head2,*head3,*head1,*head2
1 test *head1,*head2,*head3,*head1,*head2
2 test1 *head1,*head2,*head3,*head1,*head2
2 test1 *head1,*head2,*head3,*head1,*head2
Note that the LocationHead is not correct. This is because we need to add a predicate to the APPLY subquery to point at the correct depID.
SELECT a.depID, a.Name, oa.[LocationHead]
FROM #temp a
OUTER APPLY (SELECT STUFF((SELECT ',*'+LocationHead
FROM #temp
WHERE a.depID = depID
FOR XML PATH(''), TYPE
).value('.','NVARCHAR(MAX)'),1,1,'')
)oa([LocationHead]);
That produces: -
depID Name LocationHead
----------- -------------------------------------------------- -----------------------
1 test *head1,*head2,*head3
1 test *head1,*head2,*head3
1 test *head1,*head2,*head3
2 test1 *head1,*head2
2 test1 *head1,*head2
OK, it's now correct but we've got duplicate rows. This is because we have duplicate depIDs in the source data.
You can fix this in a couple of ways: -
SELECT depID, Name, LocationHead
FROM (SELECT DISTINCT depID, Name
FROM #temp
)a
OUTER APPLY (SELECT STUFF((SELECT ',*'+LocationHead
FROM #temp
WHERE a.depID = depID
FOR XML PATH(''), TYPE
).value('.','NVARCHAR(MAX)'),1,1,'')
)oa([LocationHead]);
SELECT DISTINCT a.depID, a.Name, oa.[LocationHead]
FROM #temp a
OUTER APPLY (SELECT STUFF((SELECT ',*'+LocationHead
FROM #temp
WHERE a.depID = depID
FOR XML PATH(''), TYPE
).value('.','NVARCHAR(MAX)'),1,1,'')
)oa([LocationHead]);
Both will return the same result, but I prefer the first option because the distinct will occur on the data before the nested loop, which I think will give a minute improvement in performance.
September 4, 2014 at 6:25 am
Thanks Cadavre,
I done the same but different way. I inserted data forst into temp table and do left join of that temp table with main query. But you have simplified it.
Thanks for your help.
Thanks
Abhas.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply