| Author |
 Topic  |
|
|
harlingtonthewizard
Constraint Violating Yak Guru
Australia
345 Posts |
 Posted - 02/24/2013 : 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 |
Edited by - harlingtonthewizard on 02/24/2013 23:30:26
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 02/24/2013 : 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
Australia
345 Posts |
Posted - 02/25/2013 : 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
Australia
345 Posts |
Posted - 02/25/2013 : 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
India
47099 Posts |
Posted - 02/25/2013 : 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
Australia
345 Posts |
Posted - 02/25/2013 : 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
India
47099 Posts |
Posted - 02/25/2013 : 00:43:05
|
arent they same?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
harlingtonthewizard
Constraint Violating Yak Guru
Australia
345 Posts |
Posted - 02/25/2013 : 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
India
47099 Posts |
Posted - 02/25/2013 : 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
Australia
345 Posts |
Posted - 02/25/2013 : 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
India
47099 Posts |
Posted - 02/25/2013 : 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
Australia
345 Posts |
Posted - 02/25/2013 : 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
Australia
345 Posts |
Posted - 02/25/2013 : 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
India
47099 Posts |
Posted - 02/25/2013 : 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
Australia
345 Posts |
Posted - 02/25/2013 : 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
India
47099 Posts |
Posted - 02/25/2013 : 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
Australia
345 Posts |
Posted - 02/25/2013 : 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.
|
Edited by - harlingtonthewizard on 02/25/2013 17:38:28 |
|
|
|
harlingtonthewizard
Constraint Violating Yak Guru
Australia
345 Posts |
Posted - 02/26/2013 : 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
India
47099 Posts |
Posted - 02/26/2013 : 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/
|
|
|
| |
Topic |
|
|
|
Map rows to multiple columns
