Add " as " in dynamic crosstab SQL server

Question

0.00/5 (No votes)

See more:

Hi ,
I create dynamic crosstab

SQL

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

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;

I would like to add " as " after field same this

SQL

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:

SQL

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

Posted 9-Dec-17 1:10am

Member 12857222

Updated 11-Dec-17 13:16pm

Add a Solution

2 solutions

Solution 2

The previous code has been successfully applied to a function

I would like to apply it to another function
I ran into a problem how to order

SQL

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

I tried

SQL

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

I add

SQL

order by examid

a problem

Msg 145, Level 15, State 1, Line 7
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

I also tried

SQL

over(order by examid))

But I have succeeded in using it because I do not know its way

Posted 11-Dec-17 6:00am

Member 12857222

Comments

Richard Deeming 13-Dec-17 11:55am

If you want to ask a new question, then post a new question. DO NOT post your question as a "solution"!

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Santosh kumar Pithani · Accepted Answer · 2017-12-10T17:55:00

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;