Auerelio Vasquez
asked on
Row Number or row count() ?
Good evening
I have a problem, where if you see from the image and the query, i query for an event id, which may have multiple topics and initiatives. There are mapping tables that store the responses for the multiple valued columns.
The problem is that when i want to report on the issue, i want to combine all of the topics and initiatives into one column, so that i'm only getting one row per event id. I have attached the image of how the data is returned and code for the query i'm using. is there a way to get only one row ? even if i just use row number to get all rows where row_num =1, that is not even working here, Thanks for all help!
here's the query:
I have a problem, where if you see from the image and the query, i query for an event id, which may have multiple topics and initiatives. There are mapping tables that store the responses for the multiple valued columns.
The problem is that when i want to report on the issue, i want to combine all of the topics and initiatives into one column, so that i'm only getting one row per event id. I have attached the image of how the data is returned and code for the query i'm using. is there a way to get only one row ? even if i just use row number to get all rows where row_num =1, that is not even working here, Thanks for all help!
here's the query:
select
e.id,
/*e.EventTitle,
e.EventDateStart as 'Start Date',
e.EventDateEnd as 'End Date',
e.EventNarrative as 'Summary/Narrative',
es.eventstatus as 'Event Status',*/
t.Topic,
i.Initiative,
e.EventRegionAcronym as Region
from events e
left join EventStatuses es on e.eventstatusid = es.ID
left join EventTopics et on e.id = et.eventid
left join topics t on et.topicid = t.id
left join EventInitiatives EI on e.id=ei.eventid
left join initiatives I on ei.initiativeID = I.ID
For each of the tables joining to the events table, you need to know:
If there can be multiple rows per event, then do you just want the first value found, or a list of all the values combined into one field, or something else?
If there can be multiple rows per event, then do you just want the first value found, or a list of all the values combined into one field, or something else?
ASKER
well, i'm trying to return the EventID and subsequent columns, with all of the topics values combined into one field and all of the initiative values in one field. all one one row. make sense ?
ASKER
so far, this is what i have. which returns one row per event id (e.id) but doesn't list all the topics and initiatives.....
Alter procedure [BA\039824].[sp_GetEventsbyRegion]
@start_Date DATE,
@End_Date DATE,
@Region VARCHAR(MAX)
as
with CTE_Events as
(select
e.id,
e.EventTitle,
e.EventDateStart as 'Start Date',
e.EventDateEnd as 'End Date',
e.EventNarrative as 'Summary/Narrative',
es.eventstatus as 'Event Status',
t.Topic,
i.Initiative,
e.EventRegionAcronym as Region,
row_number() OVER(Partition by e.id,t.topic,i.Initiative order by (SELECT NULL)) as Row
from events e
left join EventStatuses es on e.eventstatusid = es.ID
left join EventTopics et on e.id = et.eventid
left join topics t on et.topicid = t.id
left join EventInitiatives EI on e.id=ei.eventid
left join initiatives I on ei.initiativeID = I.ID)
select * from CTE_Events
where Row = 1
and [Start Date] between @start_Date and @End_Date
and (@Region IS NULL OR Region IN (SELECT Item FROM dbo.Split(@Region,',')))
GO
Can you post the current resultset and the expected result set you need. may be in an excel.
ASKER
Attached is spreadsheet with current results and desired results
Result-with-Desired-Results.xlsx
Result-with-Desired-Results.xlsx
Please refer the below query. i have created sample
Your query may look like below after change please refer.
CREATE TABLE #tmp
( ID int,
Topic VARCHAR(100),
Initiative VARCHAR(100)
)
INSERT INTO #tmp
VALUES
(3389,'Topic 1','Initiative 1 ')
,(3389,'Topic 2','Initiative 2')
,(3389,'Topic 3','Initiative 3')
,(3401,'Topic 1','Initiative 1')
,(3401,'Topic 2','Initiative 2')
,(3409,'Topic 1','Initiative 1')
,(3409,'Topic 2','Initiative 2')
,(3409,'Topic 3','Initiative 3')
SELECT DISTINCT ID
,STUFF((SELECT ','+t1.Topic FROM #tmp t1 where t1.ID=t2.ID ORDER BY t1.ID,t1.Topic FOR XML Path ('') ),1,1,'') AS Topic
,STUFF((SELECT ','+t1.Initiative FROM #tmp t1 where t1.ID=t2.ID ORDER BY t1.ID,t1.Topic FOR XML Path ('') ),1,1,'') AS Initiative
FROM #tmp t2
DROP TABLE #tmp
Your query may look like below after change please refer.
select
e.id AS ID,
e.EventTitle,
e.EventDateStart as 'Start Date',
e.EventDateEnd as 'End Date',
e.EventNarrative as 'Summary/Narrative',
es.eventstatus as 'Event Status',
t.Topic AS Topic,
i.Initiative AS Initiative,
e.EventRegionAcronym as Region,
row_number() OVER(Partition by e.id,t.topic,i.Initiative order by (SELECT NULL)) as Row
INTO #tmpFinalData
from events e
left join EventStatuses es on e.eventstatusid = es.ID
left join EventTopics et on e.id = et.eventid
left join topics t on et.topicid = t.id
left join EventInitiatives EI on e.id=ei.eventid
left join initiatives I on ei.initiativeID = I.ID)
--select * from CTE_Events
--where Row = 1
--and [Start Date] between @start_Date and @End_Date
--and (@Region IS NULL OR Region IN (SELECT Item FROM dbo.Split(@Region,','))
SELECT
DISTINCT
e.id
,STUFF((SELECT ','+t1.Topic FROM #tmpFinalData t1 where t1.ID=t2.ID ORDER BY t1.ID,t1.Topic FOR XML Path ('') ),1,1,'') AS Topic
,STUFF((SELECT ','+t1.Initiative FROM #tmpFinalData t1 where t1.ID=t2.ID ORDER BY t1.ID,t1.Topic FOR XML Path ('') ),1,1,'') AS Initiative
FROM #tmpFinalData t2
ASKER
that is almost it. the only issue is that initiative has repeating values, that don't really exist.....
post a screen shot.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
When I add the distinct in the stuff section, I get 2 error messages that say that order by items must appear in the select list if select distinct is specified.
We are not getting any issue with the above query. Could you please post the error.
ASKER
Running this query with error message at the bottom
select
e.id AS ID,
e.EventTitle,
e.EventDateStart as 'Start Date',
e.EventDateEnd as 'End Date',
e.EventNarrative as 'Summary/Narrative',
es.eventstatus as 'Event Status',
t.Topic AS Topic,
i.Initiative AS Initiative,
e.EventRegionAcronym as Region
INTO #tmpFinalData
from events e
left join EventStatuses es on e.eventstatusid = es.ID
left join EventTopics et on e.id = et.eventid
left join topics t on et.topicid = t.id
left join EventInitiatives EI on e.id=ei.eventid
left join initiatives I on ei.initiativeID = I.ID
--select * from CTE_Events
--where Row = 1
--and [Start Date] between @start_Date and @End_Date
--and (@Region IS NULL OR Region IN (SELECT Item FROM dbo.Split(@Region,','))
SELECT
distinct
id
,STUFF((SELECT ','+t1.Topic FROM #tmpFinalData t1 where t1.ID=t2.ID ORDER BY t1.ID,t1.Topic FOR XML Path ('') ),1,1,'') AS Topic
,STUFF((SELECT distinct ','+t1.Initiative FROM #tmpFinalData t1 where t1.ID=t2.ID ORDER BY t1.ID,t1.Initiative FOR XML Path ('') ),1,1,'') AS Initiative
FROM #tmpFinalData t2
order by id,topic
--drop table #tmpFinalData
Msg 145, Level 15, State 1, Line 27
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
select
e.id AS ID,
e.EventTitle,
e.EventDateStart as 'Start Date',
e.EventDateEnd as 'End Date',
e.EventNarrative as 'Summary/Narrative',
es.eventstatus as 'Event Status',
t.Topic AS Topic,
i.Initiative AS Initiative,
e.EventRegionAcronym as Region
INTO #tmpFinalData
from events e
left join EventStatuses es on e.eventstatusid = es.ID
left join EventTopics et on e.id = et.eventid
left join topics t on et.topicid = t.id
left join EventInitiatives EI on e.id=ei.eventid
left join initiatives I on ei.initiativeID = I.ID
--select * from CTE_Events
--where Row = 1
--and [Start Date] between @start_Date and @End_Date
--and (@Region IS NULL OR Region IN (SELECT Item FROM dbo.Split(@Region,','))
SELECT
distinct
id
,STUFF((SELECT ','+t1.Topic FROM #tmpFinalData t1 where t1.ID=t2.ID ORDER BY t1.ID,t1.Topic FOR XML Path ('') ),1,1,'') AS Topic
,STUFF((SELECT distinct ','+t1.Initiative FROM #tmpFinalData t1 where t1.ID=t2.ID ORDER BY t1.ID,t1.Initiative FOR XML Path ('') ),1,1,'') AS Initiative
FROM #tmpFinalData t2
order by id,topic
--drop table #tmpFinalData
Msg 145, Level 15, State 1, Line 27
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
ASKER
I was able to combine both of these me make a good solution. Thanks!
Open in new window