Member 12857222 Ответов: 2

Добавьте " как " в динамическую кросс-таблицу SQL server

Привет ,
Я создаю динамическую перекрестную таблицу 
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;


результат 
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;


Я хотел бы добавить " как " после поля же это
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;


Как мне это сделать?
Большое спасибо

Что я уже пробовал:

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;';

2 Ответов

Рейтинг:
12

Santosh kumar Pithani 

DECLARE @columns NVARCHAR(MAX), 
        @sql NVARCHAR(MAX);

SELECT @columns=
          STUFF((SELECT DISTINCT ', '+QUOTENAME(doc_select)+' AS g0'+
             CONVERT(varchar(10),ROW_NUMBER()over(order by doc_select)) 
   FROM  doc_sup_sch for XML Path(''),Type).value('.','Nvarchar(MAX)'),1,2,'') ;


SET @sql = N'select sch_id,examid,user_name, sch_type,' + @columns + ' from adminusers 
             a inner join doc_sup_sch d on a.user_id = d.sch_id 
                 where d.examid = 11 and org_id = 40088 
PIVOT
(
  count(doc_select) for doc_select in ('
  +(SELECT STUFF((SELECT DISTINCT ', '+QUOTENAME(doc_select) 
  FROM  doc_sup_sch for XML Path(''),Type).value('.','Nvarchar(MAX)'),1,2,''))
  + ')
) AS p;';

EXEC SP_EXECUTESQL @sql;

Рейтинг:
1

Member 12857222

Предыдущий код был успешно применен к функции

Я хотел бы применить его к другой функции
Я столкнулся с проблемой как сделать заказ

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);


SELECT @columns += N', p.' + QUOTENAME(shortname)  
  FROM (select d.shortname from examsTB d where act = 1  group by d.shortname)  x ;

 SELECT @columns=STUFF((SELECT DISTINCT ',    '+QUOTENAME(shortname)+' AS ' + QUOTENAME(DayName+' '+CONVERT(varchar(2),exammonth)+'/'+CONVERT(varchar(2),examday)) 
 FROM  examsTB where act =1  GROUP BY shortname,DayName,examday,exammonth,examid   for XML Path(''),Type).value('.','Nvarchar(MAX)'),1,2,'') ;
 

SET @sql = N'
SELECT user_name ,' + STUFF(@columns, 1, 2, '') + '
 FROM
(
select y.org_id, y.sch_id as ff, a.user_name, shortname from AdminUsers a inner join Follow_sch_exam_1 y on a.user_id = y.sch_id inner join examsTB p on p.examid = y.exam_id where y.org_id = 40088  group by y.sch_id, p.shortname, a.user_name, y.follow_up,y.org_id) x 
PIVOT
(
  count(ff) for shortname in ('

+(SELECT  STUFF((SELECT  DISTINCT ', '+QUOTENAME(shortname) FROM  examsTB where act =1  GROUP BY shortname  for XML Path(''),Type).value('.','Nvarchar(MAX)'),1,2,''))+ ')) AS p ;';


Я пытался

FROM  examsTB where act =1  GROUP BY shortname,DayName,examday,exammonth,examid order by examid  for XML Path(''),Type).value('.','Nvarchar(MAX)'),1,2,'') ;


Я добавляю: 
order by examid


проблема 
Msg 145, Level 15, State 1, Line 7
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.


Я тоже пытался 
over(order by examid))


Но я преуспел в использовании его, потому что я не знаю его путь

Richard Deeming

Если вы хотите задать новый вопрос, то напишите новый вопрос. НЕ разместите свой вопрос как "решение"!