SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Map rows to multiple columns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

harlingtonthewizard
Constraint Violating Yak Guru

Australia
352 Posts

Posted - 02/24/2013 :  23:24:41  Show Profile  Reply with Quote
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

Edited by - harlingtonthewizard on 02/24/2013 23:30:26

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/24/2013 :  23:47:18  Show Profile  Reply with Quote
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/

Go to Top of Page

harlingtonthewizard
Constraint Violating Yak Guru

Australia
352 Posts

Posted - 02/25/2013 :  00:09:09  Show Profile  Reply with Quote
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

Go to Top of Page

harlingtonthewizard
Constraint Violating Yak Guru

Australia
352 Posts

Posted - 02/25/2013 :  00:32:53  Show Profile  Reply with Quote
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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/25/2013 :  00:34:13  Show Profile  Reply with Quote
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/

Go to Top of Page

harlingtonthewizard
Constraint Violating Yak Guru

Australia
352 Posts

Posted - 02/25/2013 :  00:40:38  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/25/2013 :  00:43:05  Show Profile  Reply with Quote
arent they same?

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

Go to Top of Page

harlingtonthewizard
Constraint Violating Yak Guru

Australia
352 Posts

Posted - 02/25/2013 :  00:47:47  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/25/2013 :  00:53:31  Show Profile  Reply with Quote
thats just a matter of adding an order by

ORDER BY Comment, Action, Action Reference, Comment Type

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

Go to Top of Page

harlingtonthewizard
Constraint Violating Yak Guru

Australia
352 Posts

Posted - 02/25/2013 :  01:00:57  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/25/2013 :  01:04:27  Show Profile  Reply with Quote
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/

Go to Top of Page

harlingtonthewizard
Constraint Violating Yak Guru

Australia
352 Posts

Posted - 02/25/2013 :  01:08:27  Show Profile  Reply with Quote
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

Go to Top of Page

harlingtonthewizard
Constraint Violating Yak Guru

Australia
352 Posts

Posted - 02/25/2013 :  01:32:21  Show Profile  Reply with Quote
--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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/25/2013 :  02:03:22  Show Profile  Reply with Quote
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/

Go to Top of Page

harlingtonthewizard
Constraint Violating Yak Guru

Australia
352 Posts

Posted - 02/25/2013 :  02:16:32  Show Profile  Reply with Quote
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')
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/25/2013 :  04:13:14  Show Profile  Reply with Quote
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/

Go to Top of Page

harlingtonthewizard
Constraint Violating Yak Guru

Australia
352 Posts

Posted - 02/25/2013 :  17:33:31  Show Profile  Reply with Quote
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.


Edited by - harlingtonthewizard on 02/25/2013 17:38:28
Go to Top of Page

harlingtonthewizard
Constraint Violating Yak Guru

Australia
352 Posts

Posted - 02/26/2013 :  01:01:46  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/26/2013 :  01:05:15  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000