Tag Info

Hot answers tagged

7

SELECT GroupValue = Val, [Count] = COUNT(DISTINCT MyGroup) FROM ( SELECT MyGroup, Val = STUFF((SELECT ', ' + RTRIM(MyValue) FROM dbo.MyTable WHERE MyGroup = t.MyGroup FOR XML PATH(''), TYPE).value('.[1]','nvarchar(max)', 1, 2, '') FROM dbo.MyTable AS t ) AS x GROUP BY Val;


7

This behaviour is by design, as explained in detail on this Connect bug report. The most pertinent Microsoft reply is reproduced below for convenience (and in case the link dies at some point): Posted by Microsoft on 7/7/2008 at 9:27 AM Closing the loop . . . I've discussed this question with the Dev team. And eventually we have decided not to ...


5

Execution plans (actual, not estimated) need to be added to the Q for a definitive answer but... How Can the Same Query in Two Nearly Identical Instances Generate Two Different Execution Plans? Because, by your admission, they are not identical. Most likely explanation for the different execution plans is a variance in statistics. Table rows ...


5

You can do the recursion in a CTE from the top down carrying MarkupGroupID with you. with C as ( select P.PartGroupID, P.ParentID, P.MarkupGroupID from PartGroup as P where P.ParentID is null union all select P.PartGroupID, P.ParentID, coalesce(P.MarkupGroupID, C.MarkupGroupID) from PartGroup as P inner ...


4

CROSS APPLY takes a table valued function and 'applies' parameters from each row in the query you are applying it to. The function is evaluated once for each row and the output is implicitly joined to the source row in the record set from which the parameters were obtained. Note that this 'join' can be 1:M - with a TVF one row at source can generate ...


2

Ok, here's my two pence: SELECT header.Col1, MAX((CASE WHEN details.ordinal=1 THEN details.Col2 END)) AS row1, MAX((CASE WHEN details.ordinal=2 THEN details.Col2 END)) AS row2, -- ... and so on.. MAX((CASE WHEN details.ordinal=99 THEN details.Col2 END)) AS row99 FROM ( --- For each Col1, enumerate all the rows and return Col2 ...


1

Seems a simpler approach: SELECT GroupValue = Val, [Count] = COUNT(DISTINCT MyGroup) FROM ( SELECT MyGroup, Val = STUFF((SELECT ', ' + RTRIM(MyValue) FROM dbo.MyTable WHERE MyGroup = t.MyGroup FOR XML PATH('')), 1, 2, '') FROM dbo.MyTable AS t ) AS x GROUP BY Val;



Only top voted, non community-wiki answers of a minimum length are eligible