March 25, 2013 at 12:48 pm
Hi all,
I'm having a brain fart. What is the best way to achive the below results?
nameLab
MICROSOFT VISUAL STUDIO .NET101
MICROSOFT VISUAL STUDIO .NET118
MICROSOFT VISUAL STUDIO .NET256
MICROSOFT VISUAL STUDIO .NET367
MICROSOFT VISUAL STUDIO .NET51
MICROSOFT VISUAL STUDIO .NET901
PYTHON256
PYTHON376
PYTHON256
name lab(s)
MICROSOFT VISUAL STUDIO .NET 101, 118, 256,367,51,901PYTHON256
PYTHON376, 256
March 25, 2013 at 1:02 pm
FOR XML
If you want more specific help you need to provide more details.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 25, 2013 at 1:30 pm
Hi
This should do what you want ... I think
;with labData as (
SELECT *
FROM (VALUES
('MICROSOFT VISUAL STUDIO .NET',101)
,('MICROSOFT VISUAL STUDIO .NET',118)
,('MICROSOFT VISUAL STUDIO .NET',256)
,('MICROSOFT VISUAL STUDIO .NET',367)
,('MICROSOFT VISUAL STUDIO .NET',51)
,('MICROSOFT VISUAL STUDIO .NET',901)
,('PYTHON',256)
,('PYTHON',376)
,('PYTHON',256)
) AS D(Name, Lab)
)
SELECT a.Name + ' ' + SUBSTRING(c.Labs,3,9999)
FROM (SELECT Name FROM labData GROUP BY NAME) a -- Distinct Names
CROSS APPLY (
SELECT ', ' + CAST(Lab AS VARCHAR(10)) AS [text()]
FROM labData b
WHERE a.Name = b.Name
GROUP BY Lab -- Added to remove duplicates
ORDER BY Lab
FOR XML PATH('')
) c (labs) -- Concatenated Labs
I've always thought it would be nice to have a concatenate aggregate function, something along the lines of
SELECT AGG_CONCAT(Labs,', ') OVER (PARTITION BY Name ORDER BY Lab)
though I realise there would be issues with the sizing of the results and overflows
March 25, 2013 at 1:50 pm
I prefer his:
create table #TestData (
LabName varchar(32),
LabNo int
);
insert into #TestData
VALUES
('MICROSOFT VISUAL STUDIO .NET',101)
,('MICROSOFT VISUAL STUDIO .NET',118)
,('MICROSOFT VISUAL STUDIO .NET',256)
,('MICROSOFT VISUAL STUDIO .NET',367)
,('MICROSOFT VISUAL STUDIO .NET',51)
,('MICROSOFT VISUAL STUDIO .NET',901)
,('PYTHON',256)
,('PYTHON',376)
,('PYTHON',256);
with BaseData as (
select distinct
LabName
from
#TestData
)
select
bd.LabName,
Labs = stuff((select ', ' + cast(LabNo as varchar)
from #TestData td
where td.LabName = bd.LabName
for xml path(''),TYPE).value('.','varchar(max)'),1,2,'')
from
BaseData bd;
drop table #TestData;
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply