/*
All rows in #T1 for the same HId and with the same Priority 
should be combined into a single row in #T2. When creating a merged row, 
the Events column in #T2 should be a concatenation of all of the events 
of the distinct (HId, Priority) that were merged. 
*/
_____________________________
DDL:
_____________________________

CREATE TABLE #T1 
(
 HId int not null, 
 Priority tinyint not null, 
 [Event] varchar(30) Not Null,
 constraint [PKT1] primary key clustered 
(
 HId ASC, Priority ASC, [Event] ASC
)
)
GO
insert into #T1
select   1,5,'C'
union select 1,5,'R9'
union select 1,7,'F'
union select 1,7,'X'
union select 5,5,'C'
union select 5,5,'D'
union select 5,5,'X2'
union select 8,5,'R9'
union select 51,5,'A'
union select 2222,1,'Manual generation'
union select 2222,5,'C'
union select 2222,5,'P'
GO

CREATE TABLE #T2
(
 T2Id int identity(1,1) NOT NULL primary key,
 HId int NOT NULL,
 Priority [tinyint] NOT NULL,
 [Events] varchar(300) NOT NULL
 )
GO

___________________________
Sample data:
___________________________

SELECT * FROM #T1

HId Priority Event
HId,Priority,Event
1,5,C
1,5,R9
1,7,F
1,7,X
5,5,C
5,5,D
5,5,X2
8,5,R9
51,5,A
2222,1,Manual generation
2222,5,C
2222,5,P


SELECT * FROM #T2 

T2Id HId Priority Events
1,1,5,C + R9
2,1,7,F + X
3,5,5,C + D + X2
4,8,5,R9
5,51,5,A
6,2222,1,Manual generation
7,2222,5,C + P

Thanks in advance,

If you are on SQL Server 2000 or earlier, have a look at
http://groups.google.nl/group/microsoft.public.sqlserver.programming/browse_thread/thread/d9453cef772977ae/2d85bf366dd9e73e#2d85bf366dd9e73e
(URL may wrap) This post describes 5 methods.

If you are on SQL Server 2005 or later, have a look at
http://groups.google.nl/group/microsoft.public.sqlserver.programming/msg/8762789293c43627?dmode=source
This post describes FOR XML PATH in combination with CROSS APPLY

-- 
Gert-Jan
SQL Server MVP

dm1606 wrote:
> 
> /*
> All rows in #T1 for the same HId and with the same Priority
> should be combined into a single row in #T2. When creating a merged row,
> the Events column in #T2 should be a concatenation of all of the events
> of the distinct (HId, Priority) that were merged.
> */
> _____________________________
> DDL:
> _____________________________
> 
> CREATE TABLE #T1
> (
>  HId int not null,
>  Priority tinyint not null,
>  [Event] varchar(30) Not Null,
>  constraint [PKT1] primary key clustered
> (
>  HId ASC, Priority ASC, [Event] ASC
> )
> )
> GO
> insert into #T1
> select   1,5,'C'
> union select 1,5,'R9'
> union select 1,7,'F'
> union select 1,7,'X'
> union select 5,5,'C'
> union select 5,5,'D'
> union select 5,5,'X2'
> union select 8,5,'R9'
> union select 51,5,'A'
> union select 2222,1,'Manual generation'
> union select 2222,5,'C'
> union select 2222,5,'P'
> GO
> 
> CREATE TABLE #T2
> (
>  T2Id int identity(1,1) NOT NULL primary key,
>  HId int NOT NULL,
>  Priority [tinyint] NOT NULL,
>  [Events] varchar(300) NOT NULL
>  )
> GO
> 
> ___________________________
> Sample data:
> ___________________________
> 
> SELECT * FROM #T1
> 
> HId Priority Event
> HId,Priority,Event
> 1,5,C
> 1,5,R9
> 1,7,F
> 1,7,X
> 5,5,C
> 5,5,D
> 5,5,X2
> 8,5,R9
> 51,5,A
> 2222,1,Manual generation
> 2222,5,C
> 2222,5,P
> 
> SELECT * FROM #T2
> 
> T2Id HId Priority Events
> 1,1,5,C + R9
> 2,1,7,F + X
> 3,5,5,C + D + X2
> 4,8,5,R9
> 5,51,5,A
> 6,2222,1,Manual generation
> 7,2222,5,C + P
> 
> Thanks in advance,

Here is one solution:

SELECT DISTINCT hid, [priority], STUFF(event_list, 1, 3, '') AS [events]
FROM #T1 AS A
CROSS APPLY(SELECT ' + ' + [event]
             FROM #T1 AS B
             WHERE B.hid = A.hid
               AND B.priority = A.priority
             FOR XML PATH('')) AS C(event_list);

-- 
Plamen Ratchev
http://www.SQLStudio.com

Thank you, Gert-Jan.
Thank you, Plamen.
Your reply answers my question.

Why do you wish to destroy First Normal Form (1NF) with a concatenated
list structure?  Normal forms are the foundation of RDBMS, after
all.

Why are you formatting data in the back end?  The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end.  This is a more basic programming principle than just
SQL and RDBMS.

Get a reporting tool so you can do a sort for your lists and lay them
out on paper or a screen in a way that a human being can read them.
All you will get here is kludges with XML, cursors or other
proprietary procedural code tricks.

> Get a reporting tool so you can do a sort for your lists and lay them
> out on paper or a screen in a way that a human being can read them.
> All you will get here is kludges with XML, cursors or other
> proprietary procedural code tricks.

Why on earth would you buy (yes, spend money) on yet another product to 
learn when you can do this in the product you already have.

If you want to do pure standard SQL then fine - just get on with it, but to 
ignore the other 90% of the features in the product is an insult to your 
employers trust in you to get the full return on their investment, the money 
that would otherwise be used for their kids inheritance.

SQL Server is a data engine - stop trying to shoe horn it to being just a 
store and retrieve ISO SQL product.

--ROGGIE--


"--CELKO--" <jcelko212@earthlink.net> wrote in message 
news:3a11f667-4e51-4162-b0d7-ade53963e7a1@n35g2000yqm.googlegroups.com...
> Why do you wish to destroy First Normal Form (1NF) with a concatenated
> list structure?  Normal forms are the foundation of RDBMS, after
> all.
>
> Why are you formatting data in the back end?  The basic principle of a
> tiered architecture is that display is done in the front end and never
> in the back end.  This is a more basic programming principle than just
> SQL and RDBMS.
>
> Get a reporting tool so you can do a sort for your lists and lay them
> out on paper or a screen in a way that a human being can read them.
> All you will get here is kludges with XML, cursors or other
> proprietary procedural code tricks. 

"--CELKO--" <jcelko212@earthlink.net> wrote in message 
news:3a11f667-4e51-4162-b0d7-ade53963e7a1@n35g2000yqm.googlegroups.com...
> Why do you wish to destroy First Normal Form (1NF) with a concatenated
> list structure?  Normal forms are the foundation of RDBMS, after
> all.
>
> Why are you formatting data in the back end?  The basic principle of a
> tiered architecture is that display is done in the front end and never
> in the back end.  This is a more basic programming principle than just
> SQL and RDBMS.
>
> Get a reporting tool so you can do a sort for your lists and lay them
> out on paper or a screen in a way that a human being can read them.
> All you will get here is kludges with XML, cursors or other
> proprietary procedural code tricks.



Joe,
I know, I know... :-(
I couldn't agree with you more. Your comment and criticism are much 
welcomed. And it's good that you emphasize the importance of normalization. 
Someone has to do it...

On the other hand, this is not my choice and I don't want to... destroy 
anything. :-) Moreover, I hate to work that way, but I am paid to do that. 
AND I AM PAID TO DO IT EXACTLY THAT WAY!!! :-) There is a customer request, 
there is a project manager, there are specifications and I'm the one that 
has to write the code. This is the way db dev works in real-life 
companies...

P.S.
And Tony is right too. :-)))