Tag Info

New answers tagged

1

atThis is pretty much when bluefeet gave you as an example in the comment. DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT distinct ',' + QUOTENAME([Status]) from [EVAULTTEST].[dbo].EvidenceItems FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set ...


1

First, we need to get a list of all buckets. if you already have a table buckets with all bucket_id (as PK), this part is not needed: with buckets (bucket_id) as ( select distinct bucket_id from table_x ) , Then we get all bucket couples and the count of their common items - through a self-join: couples (bucket_a, bucket_b, cnt) as ( ...


0

Here's a "single pass" query that will get you the information you're after, but not in the format you wish: you'll get a single row with one column for each pair of buckets (and for each bucket individually). with item_counts as ( select item_id , sum(case when bucket_id = 'A' then 1 else 0 end) is_a , sum(case when bucket_id = 'B' then 1 else 0 end) ...


0

Bad answer, because in your note you say you don't want a join. But, just in case someone wants to use a join: select a.bucket_id, b.bucket_id, count(a.item_id) from mytable as a left outer join mytable as b on b.item_id=a.item_id and b.bucker_id<>a.bucket_id group by a.bucket_id, b.bucket_id order by a.bucket_id, b.bucket_id; (thinking further for ...


2

Not as it stands, you will have to add an index for it and that will never be able to scan only the 2011 column from the pivoted view - adding index onto that view is not possible and will give an error Msg 10114, Level 16, State 1, Line 17 Cannot create index on view "a.dbo.vw_PivotedTest" because it uses the PIVOT operator. Consider not indexing this ...



Top 50 recent answers are included