Map rows to multiple columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Map rows to multiple columns

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2013-02-24 : 23:24:41

Source Table

Create Table #t (SubLevelName nvarchar (512), SubLevelValue nvarchar (512))
INSERT INTO #t
VALUES
('EventComment_1', 'Event Comment 1'),
('EventComment_2', 'Event Comment 2'),
('EventComment_3', 'Event Comment 3'),
('SiteComment_1', 'Site Comment 1'),
('SiteComment_2', 'Site Comment 2'),
('ActionComment_1', 'Action 1'),
('ActionComment_1', 'Action 2'),
('ActionComment_RefID_1', 'Action Reference 1'),
('ActionComment_RefID_1', 'Action Reference 2')

Select * From #t
Drop Table #t

SubLevelName SubLevelValue
EventComment_1 Event Comment 1
EventComment_2 Event Comment 2
EventComment_3 Event Comment 3
SiteComment_1 Site Comment 1
SiteComment_2 Site Comment 2
ActionComment_1 Action 1
ActionComment_1 Action 2
ActionComment_RefID_1 Action Reference 1
ActionComment_RefID_1 Action Reference 2

The SubLevelName are fixed names as;
'EventComment_X' where x = 1 to 50.
'SiteComment_X' where x = 1 to 50.
'ActionComment_X' where x = 1 to 50.
'ActionComment_RefID_X' x = 1 to 50.

The SubLevelValue is free form text.

I am looking for the following output. There could be upto 50 Event Comments, 50 Site Comments and 50 Action Comment/Action Reference in the source table.

Required output where each EventComment_X has columns for each
ActionComment_X and ActionComment_RefID_X and each SiteComment_X has columns for each ActionComment_X and ActionComment_RefID_X .

Comment, Action, Action Reference, Comment Type

Event Comment 1, Action 1, Action Reference 1, EventComment
Event Comment 1, Action 2, Action Reference 2, EventComment
Event Comment 2, Action 1, Action Reference 1, EventComment
Event Comment 2, Action 2, Action Reference 2, EventComment
Event Comment 3, Action 1, Action Reference 1, EventComment
Event Comment 3, Action 2, Action Reference 2, EventComment
Site Comment 1, Action 1, Action Reference 1, SiteComment
Site Comment 1, Action 2, Action Reference 2, SiteComment
Site Comment 2, Action 1, Action Reference 1, SiteComment
Site Comment 2, Action 2, Action Reference 2, SiteComment

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-24 : 23:47:18

your current table has no columns to specify the relationships. unless you've such a column how do you determine which actions need to be associated to which events etc?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2013-02-25 : 00:09:09

The relationship is that SubLevelName is fixed I guess and I need the SubLevelValue data to be in columns?

I am not sure if an outer apply, cte, pivot or combination is the solution?

So I need SubLevelValue in columns where SubLevelName = EventComment_1, ActionComment_1 and ActionComment_RefID_1 then
SubLevelName = EventComment_1, ActionComment_2 and ActionComment_RefID_2

etc

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2013-02-25 : 00:32:53

I seem to be getting close. But not quite there. Here is what I have so far.

Create Table #t (SubLevelName nvarchar (512), SubLevelValue nvarchar (512))
INSERT INTO #t
VALUES
('EventComment_1', 'Event Comment 1'),
('EventComment_2', 'Event Comment 2'),
('EventComment_3', 'Event Comment 3'),
('SiteComment_1', 'Site Comment 1'),
('SiteComment_2', 'Site Comment 2'),
('ActionComment_1', 'Action 1'),
('ActionComment_1', 'Action 2'),
('ActionComment_RefID_1', 'Action Reference 1'),
('ActionComment_RefID_1', 'Action Reference 2')

Select a.SubLevelValue as [Comment], b.SubLevelValue as [Action], c.SubLevelValue as [ActionReference]
From #t a

OUTER APPLY
(SELECT SubLevelValue
From #t
Where SubLevelName LIKE 'ActionComment!__' ESCAPE '!') b

OUTER APPLY
(SELECT SubLevelValue
From #t
Where SubLevelName LIKE 'ActionComment_RefID_%') c

Where c.SubLevelValue != '' AND
SubLevelName LIKE 'EventComment_%' OR
SubLevelName LIKE 'SiteComment_%'

--Select * From #t

Drop Table #t

So I am getting a few extra rows I dont want.

Comment Action ActionReference
Event Comment 1 Action 1 Action Reference 1
Event Comment 1 Action 1 Action Reference 2
Event Comment 1 Action 2 Action Reference 1
Event Comment 1 Action 2 Action Reference 2
Event Comment 2 Action 1 Action Reference 1
Event Comment 2 Action 1 Action Reference 2
Event Comment 2 Action 2 Action Reference 1
Event Comment 2 Action 2 Action Reference 2
Event Comment 3 Action 1 Action Reference 1
Event Comment 3 Action 1 Action Reference 2
Event Comment 3 Action 2 Action Reference 1
Event Comment 3 Action 2 Action Reference 2
Site Comment 1 Action 1 Action Reference 1
Site Comment 1 Action 1 Action Reference 2
Site Comment 1 Action 2 Action Reference 1
Site Comment 1 Action 2 Action Reference 2
Site Comment 2 Action 1 Action Reference 1
Site Comment 2 Action 1 Action Reference 2
Site Comment 2 Action 2 Action Reference 1
Site Comment 2 Action 2 Action Reference 2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-25 : 00:34:13

so you just want the subblevels to be repeated for all? looks like cross join effect to me?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2013-02-25 : 00:40:38

Replacing outer apply with cross join gives me this;

Comment Action ActionReference
Event Comment 1 Action 1 Action Reference 1
Event Comment 1 Action 1 Action Reference 2
Event Comment 2 Action 1 Action Reference 1
Event Comment 2 Action 1 Action Reference 2
Event Comment 3 Action 1 Action Reference 1
Event Comment 3 Action 1 Action Reference 2
Site Comment 1 Action 1 Action Reference 1
Site Comment 1 Action 1 Action Reference 2
Site Comment 2 Action 1 Action Reference 1
Site Comment 2 Action 1 Action Reference 2
Event Comment 1 Action 2 Action Reference 1
Event Comment 1 Action 2 Action Reference 2
Event Comment 2 Action 2 Action Reference 1
Event Comment 2 Action 2 Action Reference 2
Event Comment 3 Action 2 Action Reference 1
Event Comment 3 Action 2 Action Reference 2
Site Comment 1 Action 2 Action Reference 1
Site Comment 1 Action 2 Action Reference 2
Site Comment 2 Action 2 Action Reference 1
Site Comment 2 Action 2 Action Reference 2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-25 : 00:43:05

arent they same?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2013-02-25 : 00:47:47

Yes, they are the same just in different order so not the result I am after. This is what I need.

Comment, Action, Action Reference, Comment Type

Event Comment 1, Action 1, Action Reference 1, EventComment
Event Comment 1, Action 2, Action Reference 2, EventComment
Event Comment 2, Action 1, Action Reference 1, EventComment
Event Comment 2, Action 2, Action Reference 2, EventComment
Event Comment 3, Action 1, Action Reference 1, EventComment
Event Comment 3, Action 2, Action Reference 2, EventComment
Site Comment 1, Action 1, Action Reference 1, SiteComment
Site Comment 1, Action 2, Action Reference 2, SiteComment
Site Comment 2, Action 1, Action Reference 1, SiteComment
Site Comment 2, Action 2, Action Reference 2, SiteComment

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-25 : 00:53:31

thats just a matter of adding an order by

ORDER BY Comment, Action, Action Reference, Comment Type

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2013-02-25 : 01:00:57

But the result is not correct so ordering is not going to help is it? I am getting 20 rows and I should be getting 10.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-25 : 01:04:27

quote:
Originally posted by harlingtonthewizard

But the result is not correct so ordering is not going to help is it? I am getting 20 rows and I should be getting 10.

you only told right you got the desired result?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2013-02-25 : 01:08:27

I am confused.

This is what I need.

Comment, Action, Action Reference, Comment Type

Event Comment 1, Action 1, Action Reference 1, EventComment
Event Comment 1, Action 2, Action Reference 2, EventComment
Event Comment 2, Action 1, Action Reference 1, EventComment
Event Comment 2, Action 2, Action Reference 2, EventComment
Event Comment 3, Action 1, Action Reference 1, EventComment
Event Comment 3, Action 2, Action Reference 2, EventComment
Site Comment 1, Action 1, Action Reference 1, SiteComment
Site Comment 1, Action 2, Action Reference 2, SiteComment
Site Comment 2, Action 1, Action Reference 1, SiteComment
Site Comment 2, Action 2, Action Reference 2, SiteComment

This is what I am getting.

Comment Action ActionReference
Event Comment 1 Action 1 Action Reference 1
Event Comment 1 Action 1 Action Reference 2
Event Comment 1 Action 2 Action Reference 1
Event Comment 1 Action 2 Action Reference 2
Event Comment 2 Action 1 Action Reference 1
Event Comment 2 Action 1 Action Reference 2
Event Comment 2 Action 2 Action Reference 1
Event Comment 2 Action 2 Action Reference 2
Event Comment 3 Action 1 Action Reference 1
Event Comment 3 Action 1 Action Reference 2
Event Comment 3 Action 2 Action Reference 1
Event Comment 3 Action 2 Action Reference 2
Site Comment 1 Action 1 Action Reference 1
Site Comment 1 Action 1 Action Reference 2
Site Comment 1 Action 2 Action Reference 1
Site Comment 1 Action 2 Action Reference 2
Site Comment 2 Action 1 Action Reference 1
Site Comment 2 Action 1 Action Reference 2
Site Comment 2 Action 2 Action Reference 1
Site Comment 2 Action 2 Action Reference 2

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2013-02-25 : 01:32:21

--Drop Table #t

Create Table #t (SubLevelName nvarchar (512), SubLevelValue nvarchar (512))
INSERT INTO #t
VALUES
('EventComment_1', 'Event Comment 1'),
('EventComment_2', 'Event Comment 2'),
('EventComment_3', 'Event Comment 3'),
('SiteComment_1', 'Site Comment 1'),
('SiteComment_2', 'Site Comment 2'),
('ActionComment_1', 'Action 1'),
('ActionComment_1', 'Action 2'),
('ActionComment_3', 'Action 3'),
('ActionComment_4', 'Action 4'),
('ActionComment_RefID_1', 'Action Reference 1'),
('ActionComment_RefID_1', 'Action Reference 2')

Select
--a.SubLevelName as NameComment, b.SubLevelName as NameAction, c.SubLevelName as NameRef,
a.SubLevelValue as [Comment], b.SubLevelValue as [Action], c.SubLevelValue as [ActionReference]
From #t a

OUTER APPLY
(SELECT SubLevelName, SubLevelValue
From #t
Where SubLevelName LIKE 'ActionComment!__' ESCAPE '!') b

OUTER APPLY
(SELECT SubLevelName, SubLevelValue
From #t
Where SubLevelName LIKE 'ActionComment_RefID_%') c

Where
a.SubLevelName LIKE 'EventComment_%' OR
a.SubLevelName LIKE 'SiteComment_%'

Drop Table #t

Comment Action ActionReference
Event Comment 1 Action 1 Action Reference 1
Event Comment 1 Action 1 Action Reference 2
Event Comment 1 Action 2 Action Reference 1
Event Comment 1 Action 2 Action Reference 2
Event Comment 1 Action 3 Action Reference 1
Event Comment 1 Action 3 Action Reference 2
Event Comment 1 Action 4 Action Reference 1
Event Comment 1 Action 4 Action Reference 2
Event Comment 2 Action 1 Action Reference 1
Event Comment 2 Action 1 Action Reference 2
Event Comment 2 Action 2 Action Reference 1
Event Comment 2 Action 2 Action Reference 2
Event Comment 2 Action 3 Action Reference 1
Event Comment 2 Action 3 Action Reference 2
Event Comment 2 Action 4 Action Reference 1
Event Comment 2 Action 4 Action Reference 2
Event Comment 3 Action 1 Action Reference 1
Event Comment 3 Action 1 Action Reference 2
Event Comment 3 Action 2 Action Reference 1
Event Comment 3 Action 2 Action Reference 2
Event Comment 3 Action 3 Action Reference 1
Event Comment 3 Action 3 Action Reference 2
Event Comment 3 Action 4 Action Reference 1
Event Comment 3 Action 4 Action Reference 2
Site Comment 1 Action 1 Action Reference 1
Site Comment 1 Action 1 Action Reference 2
Site Comment 1 Action 2 Action Reference 1
Site Comment 1 Action 2 Action Reference 2
Site Comment 1 Action 3 Action Reference 1
Site Comment 1 Action 3 Action Reference 2
Site Comment 1 Action 4 Action Reference 1
Site Comment 1 Action 4 Action Reference 2
Site Comment 2 Action 1 Action Reference 1
Site Comment 2 Action 1 Action Reference 2
Site Comment 2 Action 2 Action Reference 1
Site Comment 2 Action 2 Action Reference 2
Site Comment 2 Action 3 Action Reference 1
Site Comment 2 Action 3 Action Reference 2
Site Comment 2 Action 4 Action Reference 1
Site Comment 2 Action 4 Action Reference 2

Desired result;

Comment Action ActionReference
Event Comment 1 Action 1 Action Reference 1
Event Comment 1 Action 2 Action Reference 2
Event Comment 1 Action 3
Event Comment 1 Action 4
Event Comment 2 Action 1 Action Reference 1
Event Comment 2 Action 2 Action Reference 2
Event Comment 2 Action 3
Event Comment 2 Action 4
Event Comment 3 Action 1 Action Reference 1
Event Comment 3 Action 2 Action Reference 2
Event Comment 3 Action 3
Event Comment 3 Action 4
Site Comment 1 Action 1 Action Reference 1
Site Comment 1 Action 2 Action Reference 2
Site Comment 1 Action 3
Site Comment 1 Action 4
Site Comment 2 Action 1 Action Reference 1
Site Comment 2 Action 2 Action Reference 2
Site Comment 2 Action 3
Site Comment 2 Action 4

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-25 : 02:03:22

you're really making us confused. do you mean you're not geeting require result?

also what happned to actionreference values in between? is it blank value?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2013-02-25 : 02:16:32

1. In my last post I gave the test case, Table #t.
2. The code I have so far.
3. The result this code provides which is incorrect.
4. The desired result.

The reason the desired result has NULL for some ActionReference fields is because the respective Action has no action reference. If you look at the input data SubLevelName field for 'ActionComment_3' for example it does not have a corrosponding 'ActionComment_RefID_3.

There was a small error in #t in that;
('ActionComment_RefID_1', 'Action Reference 2')
should be
('ActionComment_RefID_2', 'Action Reference 2')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-25 : 04:13:14

so is your rule to map comment Ref IDs against corresponding commends? Presence of mapping id inside table would have made this much easier

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2013-02-25 : 17:33:31

It is to first map every 'Action' to every 'Comment'. So the first step looks like this;

Event Comment 1 Action 1
Event Comment 1 Action 2
Event Comment 1 Action 3
Event Comment 1 Action 4
Event Comment 2 Action 1
Event Comment 2 Action 2
Event Comment 2 Action 3
Event Comment 2 Action 4
Event Comment 3 Action 1
Event Comment 3 Action 2
Event Comment 3 Action 3
Event Comment 3 Action 4
Site Comment 1 Action 1
Site Comment 1 Action 2
Site Comment 1 Action 3
Site Comment 1 Action 4
Site Comment 2 Action 1
Site Comment 2 Action 2
Site Comment 2 Action 3
Site Comment 2 Action 4

and this code acheives this;

Drop Table #t

Create Table #t (SubLevelName nvarchar (512), SubLevelValue nvarchar (512))
INSERT INTO #t
VALUES
('EventComment_1', 'Event Comment 1'),
('EventComment_2', 'Event Comment 2'),
('EventComment_3', 'Event Comment 3'),
('SiteComment_1', 'Site Comment 1'),
('SiteComment_2', 'Site Comment 2'),
('ActionComment_1', 'Action 1'),
('ActionComment_1', 'Action 2'),
('ActionComment_3', 'Action 3'),
('ActionComment_4', 'Action 4'),
('ActionComment_RefID_1', 'Action Reference 1'),
('ActionComment_RefID_2', 'Action Reference 2')

Select a.SubLevelValue as [Comment], b.SubLevelValue as [Action]
From #t a

OUTER APPLY
(SELECT SubLevelName, SubLevelValue
From #t
Where SubLevelName LIKE 'ActionComment!__' ESCAPE '!') b

Where
a.SubLevelName LIKE 'EventComment_%' OR
a.SubLevelName LIKE 'SiteComment_%'
ORDER BY a.SubLevelName

Drop Table #t

The problem I am having is with the next part where I need to map 'Action' to Action Reference' where I only want the mapping when the SubLevelName number at the end of the name is equal. So 'ActionComment_1' = 'ActionComment_RefID_1' for example.

This should provide a final output like this;

Comment Action ActionReference
Event Comment 1 Action 1 Action Reference 1
Event Comment 1 Action 2 Action Reference 2
Event Comment 1 Action 3 NULL
Event Comment 1 Action 4 NULL
Event Comment 2 Action 1 Action Reference 1
Event Comment 2 Action 2 Action Reference 2
Event Comment 2 Action 3 NULL
Event Comment 2 Action 4 NULL
Event Comment 3 Action 1 Action Reference 1
Event Comment 3 Action 2 Action Reference 2
Event Comment 3 Action 3 NULL
Event Comment 3 Action 4 NULL
Site Comment 1 Action 1 Action Reference 1
Site Comment 1 Action 2 Action Reference 2
Site Comment 1 Action 3 NULL
Site Comment 1 Action 4 NULL
Site Comment 2 Action 1 Action Reference 1
Site Comment 2 Action 2 Action Reference 2
Site Comment 2 Action 3 NULL
Site Comment 2 Action 4 NULL

There are Four SubLevelName values each with a number on the end 1 to 50.
'EventComment_1'
'SiteComment_1'
'ActionComment_1'
'ActionComment_RefID_1'

The SubLevelName field from the input table is used for the calculation but the output result only contains the SubLevelValue data from the input table.

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2013-02-26 : 01:01:46

So I finally worked it out!

Create Table #t (SubLevelName nvarchar (512), SubLevelValue nvarchar (512))
INSERT INTO #t
VALUES
('EventComment_1', 'Event Comment 1'),
('EventComment_2', 'Event Comment 2'),
('EventComment_3', 'Event Comment 3'),
('EventComment_4', 'Event Comment 4'),
('SiteComment_1', 'Site Comment 1'),
('SiteComment_2', 'Site Comment 2'),
('ActionComment_1', 'Action 1'),
('ActionComment_2', 'Action 2'),
('ActionComment_3', 'Action 3'),
('ActionComment_4', 'Action 4'),
('ActionComment_5', 'Action 5'),
('ActionComment_RefID_1', 'Action Reference 1'),
('ActionComment_RefID_2', 'Action Reference 2'),
('ActionComment_RefID_3', 'Action Reference 3')

Select a.SubLevelValue as [Comment], b.SubLevelValue as [Action], NULL as [Note], c.SubLevelValue as [Reference],
CASE
WHEN LEFT(a.SubLevelName,5) = 'Event' THEN 'EventComment'
WHEN LEFT(a.SubLevelName,4) = 'Site' THEN 'SiteComment'
END AS CommentType
From #t a

OUTER APPLY
(SELECT SubLevelName, SubLevelValue
From #t
Where SubLevelName != '' AND SubLevelValue != '' AND
(SubLevelName LIKE 'ActionComment!__' ESCAPE '!')) b

OUTER APPLY
(SELECT SubLevelName, SubLevelValue
From #t
Where SubLevelName != '' AND SubLevelName LIKE 'ActionComment_RefID_%'
AND RIGHT(b.SubLevelName, 1) = RIGHT(SubLevelName, 1)) c

Where a.SubLevelName != '' AND a.SubLevelValue != '' AND
(a.SubLevelName LIKE 'EventComment_%' OR a.SubLevelName LIKE 'SiteComment_%')

Drop Table #t

Comment Action Note Reference CommentType
Event Comment 1 Action 1 NULL Action Reference 1 EventComment
Event Comment 1 Action 2 NULL Action Reference 2 EventComment
Event Comment 1 Action 3 NULL Action Reference 3 EventComment
Event Comment 1 Action 4 NULL NULL EventComment
Event Comment 1 Action 5 NULL NULL EventComment
Event Comment 2 Action 1 NULL Action Reference 1 EventComment
Event Comment 2 Action 2 NULL Action Reference 2 EventComment
Event Comment 2 Action 3 NULL Action Reference 3 EventComment
Event Comment 2 Action 4 NULL NULL EventComment
Event Comment 2 Action 5 NULL NULL EventComment
Event Comment 3 Action 1 NULL Action Reference 1 EventComment
Event Comment 3 Action 2 NULL Action Reference 2 EventComment
Event Comment 3 Action 3 NULL Action Reference 3 EventComment
Event Comment 3 Action 4 NULL NULL EventComment
Event Comment 3 Action 5 NULL NULL EventComment
Event Comment 4 Action 1 NULL Action Reference 1 EventComment
Event Comment 4 Action 2 NULL Action Reference 2 EventComment
Event Comment 4 Action 3 NULL Action Reference 3 EventComment
Event Comment 4 Action 4 NULL NULL EventComment
Event Comment 4 Action 5 NULL NULL EventComment
Site Comment 1 Action 1 NULL Action Reference 1 SiteComment
Site Comment 1 Action 2 NULL Action Reference 2 SiteComment
Site Comment 1 Action 3 NULL Action Reference 3 SiteComment
Site Comment 1 Action 4 NULL NULL SiteComment
Site Comment 1 Action 5 NULL NULL SiteComment
Site Comment 2 Action 1 NULL Action Reference 1 SiteComment
Site Comment 2 Action 2 NULL Action Reference 2 SiteComment
Site Comment 2 Action 3 NULL Action Reference 3 SiteComment
Site Comment 2 Action 4 NULL NULL SiteComment
Site Comment 2 Action 5 NULL NULL SiteComment

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-26 : 01:05:15

sounds like this to me


select p.[Comment],p.Action,q.SubLevelValue  AS ActionReference
from
(
Select a.SubLevelName,a.SubLevelValue as [Comment], b.SubLevelValue as [Action]
From #t a

OUTER APPLY
(SELECT SubLevelName, SubLevelValue
From #t
Where SubLevelName LIKE 'ActionComment!__' ESCAPE '!') b

Where
a.SubLevelName LIKE 'EventComment_%' OR 
a.SubLevelName LIKE 'SiteComment_%'
)p
LEFT JOIN #t q
ON  STUFF(q.SubLevelValue,1,PATINDEX('%[0-9]%',q.SubLevelValue)-1,'')= STUFF(p.Action,1,PATINDEX('%[0-9]%',p.Action)-1,'')
AND q.SubLevelName LIKE 'ActionComment_RefID_%' 
ORDER BY p.SubLevelName

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources