Fun task for the afternoon - grouping data

Question

Post reply

Fun task for the afternoon - grouping data

olibbhq

SSCarpal Tunnel

Points: 4034
More actions
July 5, 2013 at 8:53 am

#395091

Hi,
I am trying to create a table with a grouping based on a merged column. Best to explain with SQL
CREATE TABLE [dbo].[#Table_1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Region] [varchar](50) NULL,
[Prop1] [varchar](50) NULL,
[Prop2] [varchar](50) NULL,
[Prop3] [varchar](50) NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into #Table_1 values
('ANZ', 'A', 'B', 'C'),
('CAM', 'F', 'G', 'C'),
('CAM', 'A', 'B', 'C'),
('ME', 'H', 'V', 'N'),
('SAM', 'A', 'B', 'C')
select * from #Table_1
--SOME QUERY HERE to turn the data into something like this:
CREATE TABLE [dbo].[#Table_2](
[Region] [varchar](50) NULL,
[Prop1] [varchar](50) NULL,
[Prop2] [varchar](50) NULL,
[Prop3] [varchar](50) NULL)
insert into #Table_2 values
('ANZ,CAM,SAM', 'A', 'B', 'C'),
('CAM', 'F', 'G', 'C'),
('ME', 'H', 'V', 'N')
select * from #Table_2
--drop Table #Table_1
--drop Table #Table_2
I am thinking that at somepoint I need to use the XML PATH trick to concatenate the fields but I am a bit lost as to how to do this with the above senario. Any help you can pass on would be very useful.
Many Thanks,
Oliver

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

Try this:

SELECT DISTINCT

STUFF((SELECT ',' + m2.Region

FROM #Table_1 AS m2

WHERE m2.Prop1 = m1.Prop1

AND m2.Prop2 = m1.Prop2

AND m2.Prop3 = m1.Prop3

ORDER BY m2.Region

FOR XML PATH('')),1,1,'') AS Regions

,m1.Prop1

,m1.Prop2

,m1.Prop3

FROM #Table_1 AS m1

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

olibbhq SSCarpal Tunnel Points: 4034 More actions · Answer 2

Eugene,

Thank you so much, this works a treat. I was working along these lines however I was just adding the non merged rows outside the XML path and wondering why it wasnt working. Simply fantastic, thanks very much have a great weekend.

Oliver