April 8, 2014 at 12:03 am
Hi Guys,
I want to convert the data from Original Table to Reporting View like below,
I have tried but not get success yet.
plz help if anybody can.
Original Table:
================================================================
Id || Id1 || Id2 || MasterId || Obs ||Dec || Act || Status || InstanceId
================================================================
1 || 138 || 60 || 1 || Obs1 ||Dec1 || Act1 || 0|| 14
2 || 138 || 60 || 2 || Obs2 ||Dec2 || Act2 || 1|| 14
3 || 138 || 60 || 3 || Obs3 ||Dec3 || Act3 || 1|| 14
4 || 138 || 60 || 4 || Obs4 ||Dec4 || Act4 || 0|| 14
5 || 138 || 60 || 5 || Obs5 ||Dec5 || Act5 || 1|| 14
View For Reporting:
Row Header:
Id1 || Id2 || MasterId1 || Obs1 ||Desc1 ||Act1 ||StatusId1||MasterId ||Obs2 ||Desc2 ||Act2 ||StatusId2 ||MasterId3||Obs3 ||Desc3 ||Act3 ||StatusId3||MasterId4||Obs4||Desc4 ||Act4 ||StatusId4 ||MasterId5||Obs5 ||Desc5 ||Act5 ||StatusId5||InstanceId
Row Values:
138 || 60 || 1 || Obs1 ||Desc1 ||Act1 ||0 ||2 ||Obs2 ||Desc2||Act2 ||1 ||3 ||Obs3||Desc3 ||Act3 ||2 ||4||Obs4||Desc4 ||Act4 ||0 ||5 ||Obs5 ||Desc5 ||Act5 ||1 ||14
April 8, 2014 at 6:53 am
There are a couple of problems here. First off, PIVOT can only pivot one column (at a time). You could do separate pivots as subqueries for each of the columns you want in your output then combine them in the master select. But that brings up the second problem. PIVOT works by aggregating the 'value' colum. You seem to have 5 value colums (MasterId, Obs, Act and Status, InstanceId). How do you want to aggregate them? (max, min, avg, ... etc.)? Also, what is the relationship between the Id column and the MasterId column, if any?
An alternative is to do something with cross apply. Taking your data as input, I was able to produce the output you want like this:
declare @t table (
Id int
, Id1 int
, Id2 int
, MasterId int
, Obs char(4)
, Dec char(4)
, Act char(4)
, Status int
, InstanceId varchar(50)
)
insert into @t values
(1, 138,60,1,'Obs1', 'Dec1', 'Act1', 0, 14),
(2, 138,60,2,'Obs2', 'Dec2', 'Act2', 1, 14),
(3, 138,60,3,'Obs3', 'Dec3', 'Act3', 1, 14),
(4, 138,60,4,'Obs4', 'Dec4', 'Act4', 0, 14),
(5, 138,60,5,'Obs5', 'Dec5', 'Act5', 1, 14)
select t1.Id1, t1.Id2
, t1.MasterId, t1.Obs, t1.Act, t1.Status, t1.InstanceId
, t2.MasterId, t2.Obs, t2.Act, t2.Status, t2.InstanceId
, t3.MasterId, t3.Obs, t3.Act, t3.Status, t3.InstanceId
, t4.MasterId, t4.Obs, t4.Act, t4.Status, t4.InstanceId
, t5.MasterId, t5.Obs, t5.Act, t5.Status, t5.InstanceId
from @t t1
cross apply (
select max(t2.MasterId), max(t2.Obs), max(t2.Act), max(t2.Status), max(t2.InstanceId)
from @t t2
where t2.Id = 2) t2(MasterId, Obs, Act, Status, InstanceId)
cross apply (
select max(t3.MasterId), max(t3.Obs), max(t3.Act), max(t3.Status), max(t3.InstanceId)
from @t t3
where t3.Id = 3) t3(MasterId, Obs, Act, Status, InstanceId)
cross apply (
select max(t4.MasterId), max(t4.Obs), max(t4.Act), max(t4.Status), max(t4.InstanceId)
from @t t4
where t4.Id = 4) t4(MasterId, Obs, Act, Status, InstanceId)
cross apply (
select max(t5.MasterId), max(t5.Obs), max(t5.Act), max(t5.Status), max(t5.InstanceId)
from @t t5
where t5.Id = 5) t5(MasterId, Obs, Act, Status, InstanceId)
where t1.Id = 1
but this seems completely artificial to me.
Gerald Britton, Pluralsight courses
April 8, 2014 at 7:59 am
Here's another approach using partioning with cross apply. This also produces the desired result:
declare @t table (
Id int
, Id1 int
, Id2 int
, MasterId int
, Obs char(4)
, Dec char(4)
, Act char(4)
, Status int
, InstanceId varchar(50)
)
insert into @t values
(1, 138,60,1,'Obs1', 'Dec1', 'Act1', 0, 14),
(2, 138,60,2,'Obs2', 'Dec2', 'Act2', 1, 14),
(3, 138,60,3,'Obs3', 'Dec3', 'Act3', 1, 14),
(4, 138,60,4,'Obs4', 'Dec4', 'Act4', 0, 14),
(5, 138,60,5,'Obs5', 'Dec5', 'Act5', 1, 14)
; with
part as(
select id1, id2, MasterId, Obs, Act, Status, InstanceId
, ROW_NUMBER() over(partition by id1, id2 order by id) as rn
from @t
)
select t1.*, t2.*, t3.*, t4.*, t5.*
from (
select id1, id2, MasterId, Obs, Act, Status, InstanceId
from part
where rn = 1) t1
cross apply (
select MasterId, Obs, Act, Status, InstanceId
from part
where rn = 2 and part.Id1 = t1.Id1 and part.Id2 = t1.Id2 )t2
cross apply (
select MasterId, Obs, Act, Status, InstanceId
from part
where rn = 3 and part.Id1 = t1.Id1 and part.Id2 = t1.Id2 )t3
cross apply (
select MasterId, Obs, Act, Status, InstanceId
from part
where rn = 4 and part.Id1 = t1.Id1 and part.Id2 = t1.Id2)t4
cross apply (
select MasterId, Obs, Act, Status, InstanceId
from part
where rn = 5 and part.Id1 = t1.Id1 and part.Id2 = t1.Id2)t5
Results:
(5 row(s) affected)
id1 id2 MasterId Obs Act Status InstanceId MasterId Obs Act Status InstanceId MasterId Obs Act Status InstanceId MasterId Obs Act Status InstanceId MasterId Obs Act Status InstanceId
----------- ----------- ----------- ---- ---- ----------- -------------------------------------------------- ----------- ---- ---- ----------- -------------------------------------------------- ----------- ---- ---- ----------- -------------------------------------------------- ----------- ---- ---- ----------- -------------------------------------------------- ----------- ---- ---- ----------- --------------------------------------------------
138 60 1 Obs1 Act1 0 14 2 Obs2 Act2 1 14 3 Obs3 Act3 1 14 4 Obs4 Act4 0 14 5 Obs5 Act5 1 14
(1 row(s) affected)
Gerald Britton, Pluralsight courses
April 8, 2014 at 8:32 am
Notice how g.britton posted ddl and sample data in a consumable format. That is what you should do in the future. Well done g.britton and thanks.
Here is another approach to this using a traditional cross tab. You can read more about cross tabs in my signature.
with SortedValues as
(
select ID, Id1, Id2, MasterId, Obs, [dec], Act, [Status], InstanceId
, ROW_NUMBER() over (partition by Id1, Id2 order by Id) as RowNum
from @t
)
select Id1, Id2, InstanceID,
MAX(Case when RowNum = 1 then MasterID end) as MasterID1,
MAX(Case when RowNum = 1 then Obs end) as Obs1,
MAX(Case when RowNum = 1 then [Dec] end) as Dec1,
MAX(Case when RowNum = 1 then Act end) as Act1,
MAX(Case when RowNum = 1 then [Status] end) as Status1,
MAX(Case when RowNum = 2 then MasterID end) as MasterID2,
MAX(Case when RowNum = 2 then Obs end) as Obs2,
MAX(Case when RowNum = 2 then [Dec] end) as Dec2,
MAX(Case when RowNum = 2 then Act end) as Act2,
MAX(Case when RowNum = 2 then [Status] end) as Status2,
MAX(Case when RowNum = 3 then MasterID end) as MasterID3,
MAX(Case when RowNum = 3 then Obs end) as Obs3,
MAX(Case when RowNum = 3 then [Dec] end) as Dec3,
MAX(Case when RowNum = 3 then Act end) as Act3,
MAX(Case when RowNum = 3 then [Status] end) as Status3,
MAX(Case when RowNum = 4 then MasterID end) as MasterID4,
MAX(Case when RowNum = 4 then Obs end) as Obs4,
MAX(Case when RowNum = 4 then [Dec] end) as Dec4,
MAX(Case when RowNum = 4 then Act end) as Act4,
MAX(Case when RowNum = 4 then [Status] end) as Status4,
MAX(Case when RowNum = 5 then MasterID end) as MasterID5,
MAX(Case when RowNum = 5 then Obs end) as Obs5,
MAX(Case when RowNum = 5 then [Dec] end) as Dec5,
MAX(Case when RowNum = 5 then Act end) as Act5,
MAX(Case when RowNum = 5 then [Status] end) as Status5
from SortedValues
group by Id1, Id2, InstanceID
OP, you should avoid using t-sql reserved words as object names (tables, columns, etc). Things like Dec and Status are a pain to work with because you have to constantly wrap them in []. Also, they are sufficiently vague that they don't add much to your column name.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 8, 2014 at 8:40 am
I was working on a cross tabs solution but Sean beat me to do it.
I just wanted to point out that it should out perform the previous solutions because it only has to read the table once instead of five times.
April 8, 2014 at 8:52 am
Luis Cazares (4/8/2014)
I was working on a cross tabs solution but Sean beat me to do it.I just wanted to point out that it should out perform the previous solutions because it only has to read the table once instead of five times.
There is a first. You are usually super fast. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 9, 2014 at 2:26 pm
Yet another version, that doesn't limit you to having only 5 of them. This one's basically an adaptation off Sean's version.
Note that you could theoretically hit a limit on the number of columns in your recordset if you're not careful with this.
CREATE TABLE #t (
Id int
, Id1 int
, Id2 int
, MasterId int
, Obs char(4)
, Dec char(4)
, Act char(4)
, Status int
, InstanceId varchar(50)
)
insert into #t values
(1, 138,60,1,'Obs1', 'Dec1', 'Act1', 0, 14),
(2, 138,60,2,'Obs2', 'Dec2', 'Act2', 1, 14),
(3, 138,60,3,'Obs3', 'Dec3', 'Act3', 1, 14),
(4, 138,60,4,'Obs4', 'Dec4', 'Act4', 0, 14),
(5, 138,60,5,'Obs5', 'Dec5', 'Act5', 1, 14)
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = N'SELECT Id1, Id2, InstanceID'
;
SELECT @SQL = @SQL +
', MAX(CASE WHEN ID = ' + CAST(ID AS VARCHAR) + ' THEN MasterID END) AS MasterID' + CAST(ID AS VARCHAR) +
', MAX(CASE WHEN ID = ' + CAST(ID AS VARCHAR) + ' THEN Obs END) AS Obs' + CAST(ID AS VARCHAR) +
', MAX(CASE WHEN ID = ' + CAST(ID AS VARCHAR) + ' THEN [Dec] END) AS Dec' + CAST(ID AS VARCHAR) +
', MAX(CASE WHEN ID = ' + CAST(ID AS VARCHAR) + ' THEN Act END) AS Act' + CAST(ID AS VARCHAR) +
', MAX(CASE WHEN ID = ' + CAST(ID AS VARCHAR) + ' THEN [Status] END) AS Status' + CAST(ID AS VARCHAR)
FROM #t
ORDER BY ID
SET @SQL = @SQL + ' FROM #t GROUP BY Id1, Id2, InstanceID'
EXEC (@SQL)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply