April 15, 2015 at 2:37 pm
IdIncident IDIncident Description
2853459140511
2861733118512
2861733118601
2869113140511
2872349118512
2872349118601
2895400140511
2900999140511
2914574140511
2923590140511
2925735274503
2925735274504
2925735274511
April 15, 2015 at 2:54 pm
April 15, 2015 at 2:57 pm
sorry i am new on this forum and got trigger happy. I need each id with uniqe incident id on onerow with each incident across. i did try a pivot script see below but i keep getting errors about converting nvarchar to numberic
select [ID]
from
(
select [ID], [Incident ID], [Incident Description]
from VW_ILLDISC1
) x
pivot
(
max([Incident Description])
for [Incident ID] in ([C1], [C2], [C3], [C4])
April 15, 2015 at 3:02 pm
like this:
Id Incident IDincident1incident2incident3incident4
2853459 140 511
2861733 118 512601
2869113 140 511
2872349 118 512601
2895400 140 511
2900999 140 511
2914574 140 511
2923590 140 511
2925735 274 503504511
April 15, 2015 at 3:21 pm
Can you provide a setup script with sample table and sample data to reflect what you are trying to work with?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 15, 2015 at 3:28 pm
April 15, 2015 at 3:32 pm
see attachment
April 15, 2015 at 3:38 pm
It's hard to tell exactly what you are doing or working with. Please look this over for posting help: http://www.sqlservercentral.com/articles/Best+Practices/61537/
What you should do is mock a table and post the create statement, and then the inserts for your sample data. For your results, you've posted those OK, but make sure that we know where columns are. Do you have 5 columns or 3?
You can use the code format to the left of the edit box to format results. Be sure you include any errors you have with your code. Also, be sure your spacing helps us understand what's going on.
I would guess that your pivot is broken because you need to PIVOT on real data values, not the columns. I'm guessing that you want each [Incident ID] once, but your description doesn't quite make sense. What is the logic for choosing an Incident? Please be sure you check for typos when you post as they can make it difficult to understand what problem you're solving.
April 16, 2015 at 4:40 am
Hello 🙂
If you dont need them in seperate columns maybe give this a try:
--Create temp test table
CREATE TABLE #VW_ILLDISC1
(
Id INT NOT NULL,
IncidentId INT NOT NULL,
IncidentDescription NVARCHAR(10)
)
GO
--Populate temp test table
INSERT INTO #VW_ILLDISC1(Id,IncidentId,IncidentDescription)
VALUES(2853459,140,'511'),
(2861733,118,'512'),
(2861733,118,'601'),
(2869113,140,'511'),
(2872349,118,'512'),
(2872349,118,'601'),
(2895400,140,'511'),
(2900999,140,'511'),
(2914574,140,'511'),
(2923590,140,'511'),
(2925735,274,'503'),
(2925735,274,'504'),
(2925735,274,'511')
GO
DECLARE @Delimeter NVARCHAR(2) = ' ' --Choose a delimiter
SELECTDISTINCT
Id
,IncidentId
,IncidentDescription = STUFF((SELECT @Delimeter + IncidentDescription
FROM#VW_ILLDISC1 S
WHERES.Id = T.Id
ANDS.IncidentId = T.IncidentId
ORDER BY IncidentDescription
FOR XML PATH('')), 1, 1, '')
FROM #VW_ILLDISC1 T
This returns all the incidents concatenated in one column.
If you do still want to go down the Pivot route try this:
--Create temp test table
CREATE TABLE #VW_ILLDISC1
(
Id INT NOT NULL,
IncidentId INT NOT NULL,
IncidentDescription NVARCHAR(10)
)
GO
--Populate temp test table
INSERT INTO #VW_ILLDISC1(Id,IncidentId,IncidentDescription)
VALUES(2853459,140,'511'),
(2861733,118,'512'),
(2861733,118,'601'),
(2869113,140,'511'),
(2872349,118,'512'),
(2872349,118,'601'),
(2895400,140,'511'),
(2900999,140,'511'),
(2914574,140,'511'),
(2923590,140,'511'),
(2925735,274,'503'),
(2925735,274,'504'),
(2925735,274,'511')
GO
;WITH VW_ILLDISC1_Numbered AS
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY Id,IncidentId ORDER BY IncidentDescription) AS RN
FROM #VW_ILLDISC1
)
SELECTId
,IncidentId
,Incident1=ISNULL([1],'')
,Incident2=ISNULL([2],'')
,Incident3=ISNULL([3],'')
,Incident4=ISNULL([4],'')
FROMVW_ILLDISC1_Numbered c
PIVOT
(
MAX([IncidentDescription])
FOR RN IN ([1], [2], [3], [4])
) AS P
This should give you exactly the same output as that in your spreadsheet 😀
Hope it helps
April 16, 2015 at 9:12 am
THANK YOU!!! Yes the second option is the one i needed and it worked. Thanks
April 16, 2015 at 12:50 pm
Hi i may have spoken too soon. When i introduced more records that have more than 4 violations i get duplicate ids one of them see attached
;WITH VW_ILLDISC1_Numbered AS
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY [Participant Import Student ID]
, [Incident Id] ORDER BY [Incident Description]) AS RN
FROM VW_ILLDISC1 where [INCIDENT ID] in ('140', '274','118','8864','11855','13790')
)
SELECT[Participant Import Student ID]
, [Incident Id]
,Incident1=ISNULL([1],'')
,Incident2=ISNULL([2],'')
,Incident3=ISNULL([3],'')
,Incident4=ISNULL([4],'')
,Incident5=ISNULL([5],'')
,Incident6=ISNULL([6],'')
FROMVW_ILLDISC1_Numbered c
PIVOT
(
MAX([Incident Description])
FOR RN IN ([1], [2], [3], [4], [5], [6])
) AS P
ORDER BY [Participant Import Student ID]
April 16, 2015 at 12:52 pm
i may have spoken too soon. when i added new records with more than 4 violations i got duplicates on one id, see attached
;WITH VW_ILLDISC1_Numbered AS
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY [Participant Import Student ID]
, [Incident Id] ORDER BY [Incident Description]) AS RN
FROM VW_ILLDISC1 where [INCIDENT ID] in ('140', '274','118','8864','11855','13790')
)
SELECT[Participant Import Student ID]
, [Incident Id]
,Incident1=ISNULL([1],'')
,Incident2=ISNULL([2],'')
,Incident3=ISNULL([3],'')
,Incident4=ISNULL([4],'')
,Incident5=ISNULL([5],'')
,Incident6=ISNULL([6],'')
FROMVW_ILLDISC1_Numbered c
PIVOT
(
MAX([Incident Description])
FOR RN IN ([1], [2], [3], [4], [5], [6])
) AS P
ORDER BY [Participant Import Student ID]
April 16, 2015 at 1:14 pm
Hi gjuarez
I have a feeling there are more columns in VW_ILLDISC1 than the few you stated in your original post, if that is the case the select * in the CTE will bring in those extra columns and your PIVOT will group by them.
I would suggest there is a 4th column you haven't mentioned which has the same value for incident 104,510 and 511 which is why they are on the same row. To get around this change select * in the CTE to the specific fields you care about:
;WITH VW_ILLDISC1_Numbered AS
(
SELECT[Participant Import Student ID]
,[Incident Id]
,[Incident Description]
,ROW_NUMBER() OVER(PARTITION BY [Participant Import Student ID], [Incident Id] ORDER BY [Incident Description]) AS RN
FROMVW_ILLDISC1 where [INCIDENT ID] in ('140', '274','118','8864','11855','13790')
)
Let me know how you get on 😀
April 16, 2015 at 2:43 pm
Thanks Stooky for you assistance. Yes i do have other fields. i did add them individually in both select statements below but when i do it, it doesn't recognize the field with the MAX statement. i attached the how the source table look like for that particular id that is duplicating.
;WITH VW_ILLDISC1_Numbered as
(
SELECT [Incident Id],[Incident Date],[Incident Description Details],[Incident start time],[Site ID],
[Location], [Reported to Law Enforcement], [Incident created by], [Incident creation time],[Participant Import Student ID],
[Participant Student Role in Incident],[Participant Employee Role in Incident], [Consequence Action],[Consequence Assigned From Date],
[Consequence Assigned To Date],[Consequence Assigned Duration Hours], [Consequence Assigned Duration Days] ,ROW_NUMBER() OVER(PARTITION BY [Participant Import Student ID]
, [Incident Id] ORDER BY [Incident Description]) AS RN
FROM VW_ILLDISC1 where [INCIDENT ID] in ('140', '274','118','8864','11855','13790')
)
SELECT[Incident Id],[Incident Date],[Incident Description Details],[Incident start time],[Site ID],
[Location], [Reported to Law Enforcement], [Incident created by], [Incident creation time],[Participant Import Student ID],
[Participant Student Role in Incident],[Participant Employee Role in Incident], [Consequence Action],[Consequence Assigned From Date],
[Consequence Assigned To Date],[Consequence Assigned Duration Hours], [Consequence Assigned Duration Days]
,Incident1=ISNULL([1],'')
,Incident2=ISNULL([2],'')
,Incident3=ISNULL([3],'')
,Incident4=ISNULL([4],'')
/*,Incident5=ISNULL([5],'')
,Incident6=ISNULL([6],'')*/
FROMVW_ILLDISC1_Numbered c
PIVOT
(
MAX([Incident Description])
FOR RN IN ([1], [2], [3], [4])---, [5], [6])
) AS P
order BY [Participant Import Student ID]
April 16, 2015 at 3:33 pm
Hi gjuarez
Okay if you need that full dataset you will have to obtain the details after you carry out the pivot. The problem here is the PIVOT will group your data by every column you include in the CTE. Since [Incident creation time] is different for almost every row the PIVOT will split your results based on that.
To get around this you will want to restrict your CTE to the initial columns that were used and then join the results of your pivot back to your table to gather the remaining details.
Now obviously your raw data is still 6 rows so we need to use an outer apply to take the last row per [Participant Import Student ID] and [Incident ID] entered (ordering by [Incident creation time]).
This does however mean you will only see the [Incident Description Details] of the last incident added for that [Incident ID]:
CREATE TABLE #VW_ILLDISC1
(
[Incident ID]INT
,[Incident Date]DATE
,[Incident Description]NVARCHAR(10)
,[Incident Description Details]NVARCHAR(50)
,[Incident start time]NVARCHAR(30)
,[Site ID]INT
,[Location]NVARCHAR(30)
,[Reported to Law Enforcement]BIT
,[Incident created by]INT
,[Incident creation time]NVARCHAR(30)
,[Participant Import Student ID]INT
,[Participant Student Role in Incident]NVARCHAR(30)
,[Participant Employee Role in Incident]NVARCHAR(30)
,[Consequence Action]NVARCHAR(30)
,[Consequence Assigned From Date]NVARCHAR(30)
,[Consequence Assigned To Date]NVARCHAR(30)
,[Consequence Assigned Duration Hours]INT
,[Consequence Assigned Duration Days]INT
)
--Insert data
INSERT INTO #VW_ILLDISC1
VALUES
(13790,'10/03/2015','103','coments, comments','10/03/2015 14:39',25,NULL,0,10003094,'10/03/2015 14:39',2927749,'Offender','Staff','SUSP','11/03/2015 00:00','17/03/2015 00:00',0,5),
(13790,'10/03/2015','104','coments, comments','12/03/2015 14:53',25,NULL,0,10003094,'12/03/2015 14:53',2927749,'Offender','Staff','SUSP','11/03/2015 00:00','17/03/2015 00:00',0,5),
(13790,'10/03/2015','501','coments, comments','12/03/2015 14:52',25,NULL,0,10003094,'12/03/2015 14:52',2927749,'Offender','Staff','SUSP','11/03/2015 00:00','17/03/2015 00:00',0,5),
(13790,'10/03/2015','504','coments, comments','12/03/2015 14:54',25,NULL,0,10003094,'12/03/2015 14:54',2927749,'Offender','Staff','SUSP','11/03/2015 00:00','17/03/2015 00:00',0,5),
(13790,'10/03/2015','510','coments, comments','12/03/2015 14:53',25,NULL,0,10003094,'12/03/2015 14:53',2927749,'Offender','Staff','SUSP','11/03/2015 00:00','17/03/2015 00:00',0,5),
(13790,'10/03/2015','511','coments, comments','12/03/2015 14:53',25,NULL,0,10003094,'12/03/2015 14:53',2927749,'Offender','Staff','SUSP','11/03/2015 00:00','17/03/2015 00:00',0,5)
--Prepare CTE containing only the fields we want to pivot on
;WITH VW_ILLDISC1_Numbered AS
(
SELECT[Participant Import Student ID]
,[Incident Id]
,[Incident Description]
,ROW_NUMBER() OVER(PARTITION BY [Participant Import Student ID], [Incident Id] ORDER BY [Incident Description]) AS RN
FROM#VW_ILLDISC1 WHERE [INCIDENT ID] in ('140', '274','118','8864','11855','13790')
)
SELECTDetails.*
,Incident1 = ISNULL([1],'')
,Incident2 = ISNULL([2],'')
,Incident3 = ISNULL([3],'')
,Incident4 = ISNULL([4],'')
,Incident5 = ISNULL([5],'')
,Incident6 = ISNULL([6],'')
FROM VW_ILLDISC1_Numbered c
PIVOT
(
MAX([Incident Description])
FOR RN IN ([1], [2], [3], [4], [5], [6])
) AS P
--Gather the incident details
OUTER APPLY (
SELECTTOP 1
[Incident Id],[Incident Date],[Incident Description Details],[Incident start time],[Site ID],
[Location], [Reported to Law Enforcement], [Incident created by], [Incident creation time],[Participant Import Student ID],
[Participant Student Role in Incident],[Participant Employee Role in Incident], [Consequence Action],[Consequence Assigned From Date],
[Consequence Assigned To Date],[Consequence Assigned Duration Hours], [Consequence Assigned Duration Days]
FROM#VW_ILLDISC1 A
WHEREA.[Participant Import Student ID]= P.[Participant Import Student ID]
ANDA.[Incident ID]= P.[Incident ID]
ORDER BY [Incident creation time] DESC
)AS DETAILS
ORDER BY [Participant Import Student ID]
For future reference if you could script your table and put it with the data you attached that would save a lot of time 😛
Just remove the #'s for the script to work for you.
Let me know how you get on.
EDIT: Also the reason your script didn't work is you didn't include [Incident Description] in your CTE 🙂 However even if you did include it, you would still have run into the PIVOT grouping problem
EDIT2: If you did want to pivot the [Incident Description Details] that is possible too:
;WITH VW_ILLDISC1_Numbered AS
(
SELECT[Participant Import Student ID]
,[Incident Id]
,[Incident Description]
,[Incident Description Details] --Added for second pivot
,ROW_NUMBER() OVER(PARTITION BY [Participant Import Student ID], [Incident Id] ORDER BY [Incident Description]) AS RN
,-ROW_NUMBER() OVER(PARTITION BY [Participant Import Student ID], [Incident Id] ORDER BY [Incident Description]) AS RN2 --Added for second pivot
FROM#VW_ILLDISC1 WHERE [INCIDENT ID] in ('140', '274','118','8864','11855','13790')
)
SELECT *
FROM
(
SELECTP2.[Participant Import Student ID]
,P2.[Incident ID]
,Incident1= MAX(ISNULL([1],''))
,Incident1Details= MAX(ISNULL([-1],''))
,Incident2= MAX(ISNULL([2],''))
,Incident2Details= MAX(ISNULL([-2],''))
,Incident3= MAX(ISNULL([3],''))
,Incident3Details= MAX(ISNULL([-3],''))
,Incident4= MAX(ISNULL([4],''))
,Incident4Details= MAX(ISNULL([-4],''))
,Incident5= MAX(ISNULL([5],''))
,Incident5Details= MAX(ISNULL([-5],''))
,Incident6= MAX(ISNULL([6],''))
,Incident6Details= MAX(ISNULL([-6],''))
FROM VW_ILLDISC1_Numbered c
PIVOT
(
MAX([Incident Description])
FOR RN IN ([1], [2], [3], [4], [5], [6])
) AS P
PIVOT
(
MAX([Incident Description Details])
FOR RN2 IN ([-1], [-2], [-3], [-4], [-5], [-6])
) AS P2
GROUP BY P2.[Participant Import Student ID]
,P2.[Incident ID]
)AS P
--Gather the incident details
OUTER APPLY (
SELECTTOP 1
[Incident Date],[Incident start time],[Site ID],
[Location], [Reported to Law Enforcement], [Incident created by], [Incident creation time],
[Participant Student Role in Incident],[Participant Employee Role in Incident], [Consequence Action],[Consequence Assigned From Date],
[Consequence Assigned To Date],[Consequence Assigned Duration Hours], [Consequence Assigned Duration Days]
FROM#VW_ILLDISC1 A
WHEREA.[Participant Import Student ID]= P.[Participant Import Student ID]
ANDA.[Incident ID]= P.[Incident ID]
ORDER BY [Incident creation time] DESC
)AS DETAILS
ORDER BY [Participant Import Student ID]
/*
--This returns:
Participant Import Student ID Incident ID Incident1 Incident1Details Incident2 Incident2Details Incident3 Incident3Details Incident4 Incident4Details Incident5 Incident5Details Incident6 Incident6Details Incident Date Incident Description Details Incident start time Site ID Location Reported to Law Enforcement Incident created by Incident creation time Participant Student Role in Incident Participant Employee Role in Incident Consequence Action Consequence Assigned From Date Consequence Assigned To Date Consequence Assigned Duration Hours Consequence Assigned Duration Days
----------------------------- ----------- ---------- -------------------------------------------------- ---------- -------------------------------------------------- ---------- -------------------------------------------------- ---------- -------------------------------------------------- ---------- -------------------------------------------------- ---------- -------------------------------------------------- ------------- -------------------------------------------------- ------------------------------ ----------- ------------------------------ --------------------------- ------------------- ------------------------------ ------------------------------------ ------------------------------------- ------------------------------ ------------------------------ ------------------------------ ----------------------------------- ----------------------------------
2927749 13790 103 a, comments 104 b, comments 501 c, comments 504 d, comments 510 e, comments 511 f, comments 2015-10-03 a, comments 10/03/2015 14:39 25 NULL 0 10003094 10/03/2015 14:39 Offender Staff SUSP 11/03/2015 00:00 17/03/2015 00:00 0 5
*/
Hopefully that will give you everything you need. There are some much more knowledgeable people on here than me but hopefully this solution works for you 😀
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply