October 3, 2015 at 4:20 am
Hello All,
Is there an alternative to this update query mentioned below.
DROP TABLE #FinalMeetings
CREATE TABLE #FinalMeetings
( RowNumber INT IDENTITY(1,1), MeetingType VARCHAR(60),Country VARCHAR(200), VoteFlag VARCHAR(50),
CountryList VARCHAR(MAX),MeetingList VARCHAR(MAX),Votes VARCHAR(MAX))
INSERT INTO #FinalMeetings VALUES ('Annual','Korea','Voted',NULL,NULL,NULL)
INSERT INTO #FinalMeetings VALUES ('Semi-Annual','China','Unvoted',NULL,NULL,NULL)
INSERT INTO #FinalMeetings VALUES ('Monthly','Japan','Unoted',NULL,NULL,NULL)
INSERT INTO #FinalMeetings VALUES ('Annual','Russia','*',NULL,NULL,NULL)
INSERT INTO #FinalMeetings VALUES ('Weekly','South Africa','Voted',NULL,NULL,NULL)
INSERT INTO #FinalMeetings VALUES ('Weekly','Mexico','Voted',NULL,NULL,NULL)
INSERT INTO #FinalMeetings VALUES ('Monthly','USA','Voted',NULL,NULL,NULL)
INSERT INTO #FinalMeetings VALUES ('Annual','Korea','*',NULL,NULL,NULL)
INSERT INTO #FinalMeetings VALUES ('Semi-Annual','China','*',NULL,NULL,NULL)
INSERT INTO #FinalMeetings VALUES ('Weekly','Japan','Voted',NULL,NULL,NULL)
UPDATE pm
SET pm.MeetingList = STUFF((SELECT ' || ' + CAST(temp.MeetingType AS VARCHAR(200)) [text()]
FROM (SELECT DISTINCT MeetingType FROM #FinalMeetings) temp
FOR XML PATH(''), TYPE)
.value('.','VARCHAR(MAX)'),2,2,' ')
,pm.CountryList = STUFF((SELECT ' || ' + CAST(temp.Country AS VARCHAR(200)) [text()]
FROM (SELECT DISTINCT Country FROM #FinalMeetings) temp
FOR XML PATH(''), TYPE)
.value('.','VARCHAR(MAX)'),2,2,' ')
,pm.Votes = STUFF((SELECT ' || ' + CAST(temp.VoteFlag AS VARCHAR(200)) [text()]
FROM (SELECT DISTINCT VoteFlag FROM #FinalMeetings) temp
FOR XML PATH(''), TYPE)
.value('.','VARCHAR(MAX)'),2,2,' ')
FROM #FinalMeetings pm
select * from #FinalMeetings
God Bless all
October 3, 2015 at 5:01 am
Quick question, is this query really producing the desired result set when it is missing the filtering in the XML subqueries?
😎
October 3, 2015 at 7:51 am
Yes It is giving the desired result set .
Basically the countryList, meetingList and Votes are the three columns which populate the 3 different drop down in application website.
This is the sample data of what i will be showing in drop down. In live environment the number of these values can be very high.
These values are based on the few input parameters like meetingDate (start and end), company name , etc?
Cheers
October 3, 2015 at 9:36 am
Looks like a huge waste of space not to mention a gross violation of the 1st normal form. The queries that would be need to be used to parse those lists wouldn't be a joy to behold either...
If all rows have the same MeetingList, the same CountryList & then same Votes, why not simply create 3 separate table for the 3 lists and populate your pick lists from there?
October 3, 2015 at 11:45 am
Hi Jason - Thanks for your feedback.
Please note this is a dummy data to understand what is the desired output.
the live code selects the data from different tables and the required results are inserted in temp table - #FinalMeetings.
As i mentioned above - countryList, meetingList and Votes are the three columns which populate the 3 different drop down in application website.
I just want to know can i get the required output using an alternative T-SQL code which is not as performance taxing as XML path.
Thanks.
October 3, 2015 at 12:15 pm
For converting row data into a concatenated string, FOR XML is as good as it gets in SQL Server.
The real performance problem, in your current code, is that you're executing all 3 FOR XMLs for every row in your dataset. So, if yo have a 1,000 rows you're executing 3,000 FOR XMLs.
You'd be better off running them each, just a single time, dump the results into a temp table or table variable and then joining to that.
October 3, 2015 at 12:39 pm
Jason even if i do it seperately and then join , then also SQL Server will have to use 3000 times XML path .
All, if there is an alternative to XML path to get the desired output, please let me know.
Thanks peeps.
October 3, 2015 at 2:02 pm
myzus2004 (10/3/2015)
Jason even if i do it seperately and then join , then also SQL Server will have to use 3000 times XML path .
No, if you insert these (SELECT DISTINCT Country FROM #FinalMeetings) temp FOR XML PATH('') into a temp table, SQL has to do each one once (there's no filtering, and the subqueries don't depend on the outer row), so three FOR XMLs, then you join to each of the three temp tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 3, 2015 at 2:33 pm
Thanks Gail, I will try this on Monday.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply