Hi ,
I create dynamic crosstab
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(doc_select)
FROM (select d.doc_select from doc_sup_sch d group by d.doc_select) x ;
SET @sql = N'
SELECT sch_id,examid,user_name, sch_type,' + STUFF(@columns, 1, 2, '') + '
FROM
(
select sch_id,examid,user_name, sch_type, doc_select from adminusers a inner join doc_sup_sch d on a.user_id = d.sch_id
where d.examid = 11 and org_id = 40088 ) j
PIVOT
(
count(doc_select) for doc_select in ('
+ STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
+ ')
) AS p;';
PRINT @sql;
EXEC sp_executesql @sql;
The Result
SELECT sch_id,examid,user_name, sch_type,p.[جيد], p.[مناسب جدا], p.[غير مناسب], p.[مناسب]
FROM
(
select sch_id,examid,user_name, sch_type, doc_select from adminusers a inner join doc_sup_sch d on a.user_id = d.sch_id
where d.examid = 11 and org_id = 40088 ) j
PIVOT
(
count(doc_select) for doc_select in ([جيد],[مناسب جدا],[غير مناسب],[مناسب])
) AS p;
I would like to add " as " after field same this
SELECT sch_id,examid,user_name, sch_type,p.[جيد] as g01, p.[مناسب جدا] as g02, p.[غير مناسب] as g03, p.[مناسب] as g04
FROM
(
select sch_id,examid,user_name, sch_type, doc_select from adminusers a inner join doc_sup_sch d on a.user_id = d.sch_id
where d.examid = 11 and org_id = 40088 ) j
PIVOT
(
count(doc_select) for doc_select in ([جيد],[مناسب جدا],[غير مناسب],[مناسب])
) AS p;
How do I do that .
Thank Very much
What I have tried:
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(doc_select) + 'g0' + CONVERT(varchar(10), doc_select_id)
FROM (select d.doc_select from doc_sup_sch d group by d.doc_select) x ;
SET @sql = N'
SELECT sch_id,examid,user_name, sch_type,' + STUFF(@columns, 1, 2, '') + '
FROM
(
select sch_id,examid,user_name, sch_type, doc_select from adminusers a inner join doc_sup_sch d on a.user_id = d.sch_id
where d.examid = 11 and org_id = 40088 ) j
PIVOT
(
count(doc_select) for doc_select in ('
+ STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
+ ')
) AS p;';