SQL Server 2008 R2: dynamic query for pivot table with where and having argument
Note . This post changes slightly from the previous post.
I have the following table with details as shown below in this example.
Example :
Table: test
create table test
(
cola varchar(10),
colb varchar(10),
colc varchar(10)
);
Insert
insert into test values('101','1234','A1');
insert into test values('101','4321','A2');
insert into test values('201','5678','A3');
insert into test values('301','8765','A4');
insert into test values('401','9877','A1');
insert into test values('101','9997','A6');
insert into test values('201','2277','A1');
insert into test values('201','1577','A5');
Note . Now I want to show only those records where the cola
values belong colc
. If the user passes values colc
as a parameter to a stored procedure, then it must match the exact value colc
to which the cola
value belongs .
Expected Output :
If the user passes A1,A2,A6
, then the result should be:
cola A1 A2 A6
--------------------
101 1 1 1
Note . In the above result, the entry 101
appears because it belongs to A1,A2,A6
no other values. 201
is not displayed because this also applies to A1,A3
and A5
.
If the user passed A1
, then the result should be:
cola A1
--------
401 1
Note . In the above result, the entry 401
appears because it belongs A1
.
My attempt:
Pivot Query :
DECLARE @Stuff varchar(max) = 'A1'
DECLARE @Sql varchar(max)
SET @Sql = 'SELECT cola,' +@Stuff+ '
from
(
select cola,colc
from test
where colc in(''A1'')
group by cola,colc
having count(distinct colc) = 1
)p
PIVOT
(
COUNT(colc)
FOR colc IN ('+@Stuff+')
)AS pvt'
PRINT(@Sql)
EXEC(@Sql)
Getting the result :
cola A1
----------
101 1
401 1
You can go here for practice:
SQL Fiddle:
source to share
You can use this:
DECLARE @Stuff varchar(max) = 'A1'
DECLARE @Sql varchar(max)
DECLARE @totalparam int = 1
SET @Sql = 'SELECT cola,' +@Stuff+ '
from
(
select cola,colc
from test a
where colc in(''A1'')
group by cola,colc
having (select count(colc) from test b where b.cola = a.cola ) = @totalparam
)p
PIVOT
(
COUNT(colc)
FOR colc IN ('+@Stuff+')
)AS pvt'
PRINT(@Sql)
EXEC(@Sql)
source to share
Try it.
DECLARE @Stuff VARCHAR(max) = 'A1,A2'--'A1'
DECLARE @Sql NVARCHAR(max),
@cnt INT
SELECT @cnt = Count(1)
FROM (SELECT Cast ('<M>' + Replace(@Stuff, ',', '</M><M>') + '</M>' AS XML) AS Data) AS A
CROSS APPLY Data.nodes ('/M') AS Split(a)
SET @Sql = 'SELECT cola,' + @Stuff+ ' from
(SELECT a.cola, a.colc
FROM test a
JOIN (SELECT cola
FROM test
GROUP BY cola
HAVING Count(DISTINCT colc) = '
+ CONVERT(VARCHAR(30), @cnt)+ ') B
ON a.cola = b.cola
where colc in (select rtrim(ltrim(Split.a.value(''.'', ''VARCHAR(100)'')))
FROM (SELECT Cast (''<M>'' + Replace('''
+ @Stuff + ''', '','', ''</M><M>'') + ''</M>'' AS XML) AS Data) AS A
CROSS APPLY Data.nodes (''/M'') AS Split(a) )
)p
PIVOT
(COUNT(colc) FOR colc IN (' + @Stuff+ ')
)AS pvt'
EXEC Sp_executesql @Sql
OUTPUT:
(for input line 'A1, A2')
+-----+----+----+
|cola | A1 | A2 |
+-----+----+----+
|101 | 1 | 1 |
+-----+----+----+
(for input line 'A1')
+-----+----+
|cola | A1 |
+-----+----+
|401 | 1 |
+-----+----+
source to share