Another pivot query?

1

I have my query results as follows:
CREATE TABLE [dbo].[Proficiency](
[student] nvarchar NOT NULL,
[esl_year] nvarchar NOT NULL,
[prof_level] nvarchar NOT NULL,

[code] nvarchar NOT NULL,
[rownumber] nvarchar NOT NULL
) ON [PRIMARY]

GO

INSERT INTO Proficiency(student, esl_year, prof_level, code, rownumber) VALUES ('A','2012','LP4','Y1','1')
INSERT INTO Proficiency(student, esl_year, prof_level, code, rownumber) VALUES ('A','2013','LP3','Y1','1')
INSERT INTO Proficiency(student, esl_year, prof_level, code, rownumber) VALUES ('A','2014','LP4','Y1','1')
INSERT INTO Proficiency(student, esl_year, prof_level, code, rownumber) VALUES ('A','2015','LP5','Y1','1')
INSERT INTO Proficiency(student, esl_year, prof_level, code, rownumber) VALUES ('A','2016','LP5','Y1','1')
INSERT INTO Proficiency(student, esl_year, prof_level, code, rownumber) VALUES ('A','2017','LP5','Y1','1')

How do I write the query so that the resulting row looks like this?
A 2012 LP4 2013 LP3 2014 LP4 2015 LP5 2016 LP5 2017 LP5 Y1 1

Thanks.

2

in one column?

3

I think this should work...


CREATE TABLE [dbo].[#Proficiency](
[student] nvarchar(4) NOT NULL,
[esl_year] nvarchar(4) NOT NULL,
[prof_level] nvarchar(4) NOT NULL,

[code] nvarchar(4) NOT NULL,
[rownumber] nvarchar(4) NOT NULL
) ;

GO

INSERT INTO #Proficiency(student, esl_year, prof_level, code, rownumber) VALUES ('A','2012','LP4','Y1','1');
INSERT INTO #Proficiency(student, esl_year, prof_level, code, rownumber) VALUES ('A','2013','LP3','Y1','1');
INSERT INTO #Proficiency(student, esl_year, prof_level, code, rownumber) VALUES ('A','2014','LP4','Y1','1');
INSERT INTO #Proficiency(student, esl_year, prof_level, code, rownumber) VALUES ('A','2015','LP5','Y1','1');
INSERT INTO #Proficiency(student, esl_year, prof_level, code, rownumber) VALUES ('A','2016','LP5','Y1','1');
INSERT INTO #Proficiency(student, esl_year, prof_level, code, rownumber) VALUES ('A','2017','LP5','Y1','1');
INSERT INTO #Proficiency(student, esl_year, prof_level, code, rownumber) VALUES ('B','2016','LP5','Y1','1');
INSERT INTO #Proficiency(student, esl_year, prof_level, code, rownumber) VALUES ('B','2017','LP5','Y1','1');

go

create table #temp
(
[student] nvarchar(4) NOT NULL,
[esl_year] nvarchar(4) NOT NULL,
[code] nvarchar(4) NOT NULL,
[rownumber] nvarchar(4) NOT NULL,
[Val] nvarchar(4) NOT NULL,
[Type] varchar(20),
[rn] int

Primary key(student, esl_year, Val)

)


;with cte
as
(

select a.student
,a.esl_year
,a.prof_level
,a.code 
,a.rownumber 
,a.esl_year  [Val]
,'ESL_Year' [Type]

from #Proficiency a 

union all

select a.student
,a.esl_year
,a.prof_level
,a.code 
,a.rownumber 
,a.prof_level  [Val]
,'PROF_LEVEL' [Type]

from #Proficiency a
)


insert #temp 
select
 a.student 
,a.esl_year
--,a.prof_level
,a.code
,a.rownumber 
,a.Val 
,  a.[type] +  '_' +   cast(dense_rank() over(partition by a.student  order by esl_year) as varchar(3)) [Type]
,cast(dense_rank() over(partition by a.student  order by esl_year) as varchar(3)) [rn]
from cte a

go

select * from #temp 

go

DECLARE @cols AS NVARCHAR(MAX),@query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT   ',' + QUOTENAME(c.[Type] ) 
            FROM #temp c
            group by [type], [rn] 
            order by [rn], [Type] 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');


set @query = 'SELECT student, ' + @cols + '
				, [code], [rownumber]	
            from 
            (
            select a.student,  a.[code], a.[rownumber],  a.[type], a.[val]
            from #temp a
            
            ) a
            pivot 
            (
                 max([val])
                for Type in (' + @cols + ')
            ) p; '

execute(@query);

go


drop table #Proficiency;
drop table #temp
4

hi

looks like there are many ways to do this

I have chosen PIVOT operator

Create Data Script
I have added extra column combCols which combines data from
esl_year and prof_level into one column combCols

then i used pivot

Create Data 
drop table #Proficiency
go

CREATE TABLE #Proficiency
(
[student] nvarchar(100) NOT NULL,
[esl_year] nvarchar(100) NOT NULL,
[prof_level] nvarchar(100) NOT NULL,
[code] nvarchar(100) NOT NULL,
[rownumber] nvarchar(100) NOT NULL,
[combCols] nvarchar(100) NOT NULL
) ON [PRIMARY]
GO

INSERT INTO #Proficiency (student, esl_year, prof_level, code, rownumber,combCols) VALUES ('A','2012','LP4','Y1','1',' 2012 LP4 ')
INSERT INTO #Proficiency (student, esl_year, prof_level, code, rownumber,combCols) VALUES ('A','2013','LP3','Y1','1',' 2013 LP3 ')
INSERT INTO #Proficiency (student, esl_year, prof_level, code, rownumber,combCols) VALUES ('A','2014','LP4','Y1','1',' 2014 LP4 ')
INSERT INTO #Proficiency (student, esl_year, prof_level, code, rownumber,combCols) VALUES ('A','2015','LP5','Y1','1',' 2015 LP5 ')
INSERT INTO #Proficiency (student, esl_year, prof_level, code, rownumber,combCols) VALUES ('A','2016','LP5','Y1','1',' 2016 LP5 ')
INSERT INTO #Proficiency (student, esl_year, prof_level, code, rownumber,combCols) VALUES ('A','2017','LP5','Y1','1',' 2017 LP5 ')
go

Pivot Script

SQL Script 
SELECT student, 
       [ 2012 lp4 ], 
       [ 2013 lp3 ], 
       [ 2014 lp4 ], 
       [ 2015 lp5 ], 
       [ 2016 lp5 ], 
       [ 2017 lp5 ], 
       code, 
       rownumber 
FROM   (SELECT student, 
               code, 
               rownumber, 
               combcols 
        FROM   #proficiency) AS b 
       PIVOT ( Min(combcols) 
             FOR [combcols] IN ( [ 2012 LP4 ], 
                                 [ 2013 LP3 ], 
                                 [ 2014 LP4 ], 
                                 [ 2015 LP5 ], 
                                 [ 2016 LP5 ], 
                                 [ 2017 LP5  ] ) ) AS a

Output

Summary

image
image.png1730×157 3.71 KB