Make row to column on SQL Server

Question

If I have table 1 like this

table 1

and in view I want make my table 2 like this

table 2 ]

so the table is about score on school, we put the score, so the table will be like table 1, but if we want to view table 1, i want to make view be like table 2

The name will be one and the score view become column and the score will be flexible, we can add how many score and the view will be look like in table 2

You should use dynamic pivot ... make a variable nvarchar, set it as a query with pivot and execute it, give it a try with help of google, there is plenty of examples out there, if you fail come back with what you have tried and i'll help you — Veljko89, Mar 27 '16 at 10:04
You really shouldn't be doing this in the database. Pivots in the database are incredibly problematic and never really work right. Keep the table you have and reformat the data in the application. — Jonathan Allen, Mar 27 '16 at 12:40
Possible duplicate of [SQL Server dynamic PIVOT query?](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — James Z, Mar 27 '16 at 17:04

gofr1 · Answer 1 · 2016-03-27T19:52:59.513

So, you can try this:

CREATE TABLE table1 (
  NoInduk int, 
  Nama nvarchar(50), 
  [Code] char(1),
  Score int)

INSERT INTO table1 VALUES
(2281, 'Ali Peny Lukito', 'U', 69),
(2281, 'Ali Peny Lukito', 'U', 80),
(2281, 'Ali Peny Lukito', 'U', 96),
(2281, 'Ali Peny Lukito', 'U', 90),
(2281, 'Ali Peny Lukito', 'U', 86),
(2281, 'Ali Peny Lukito', 'U', 80),
(2281, 'Ali Peny Lukito', 'U', 80),
(2304, 'Can Wong', 'U', 80),
(2304, 'Can Wong', 'U', 72),
(2304, 'Can Wong', 'U', 86),
(2304, 'Can Wong', 'U', 90),
(2304, 'Can Wong', 'U', 86),
(2304, 'Can Wong', 'U', 83)

and then use dynamic SQL with CTE and PIVOT:

DECLARE @num INT,
        @u nvarchar(512),
        @sql_q nvarchar(max)

SELECT TOP 1 @num = COUNT(Score)
FROM table1
GROUP BY NoInduk
ORDER BY COUNT(Score) DESC

;WITH num AS (
SELECT @num as n
UNION ALL
SELECT n-1
FROM num
WHERE n > 1
)
SELECT @u = STUFF((
SELECT ',U' + CAST(n as nvarchar(5))
FROM num
ORDER BY n ASC
FOR XML PATH ('')),1,1,'')

SET @sql_q = 
';WITH cte AS (
SELECT ROW_NUMBER() OVER(PARTITION BY Nama ORDER BY Score DESC) AS Row,
       NoInduk,
       Nama,
       [Code],
       Score
FROM table1
)

SELECT NoInduk,[Nama],' + @u + 
' FROM
(
SELECT NoInduk,
       Nama,
       [Code] + CAST ([Row] as nvarchar(5)) as [Code],
       Score
FROM cte
) d
pivot
(
SUM(Score) for [Code] in (' +@u +  
')) piv
ORDER BY NoInduk, [Nama];'

EXEC sp_executesql @sql_q

Results:

NoInduk Nama            U1  U2  U3  U4  U5  U6  U7
2281    Ali Peny Lukito 96  90  86  80  80  80  69
2304    Can Wong        90  86  86  83  80  72  NULL

While impressive, I would warn people not to actually use that code. You'll have to rewrite it every time a new value is added for a given person. — Jonathan Allen, Mar 27 '16 at 12:40
if i using code like that, the score will always only 6 what i want is flexible, like in the school, in semester 1 the teacher give 6x exams and then, in semester 2, the teacher give 10x exams — David Yusuf, Mar 27 '16 at 18:49

score 0 · Answer 2 · answered Mar 27 '16 at 10:09

Your table 2 is looks like pivot table, and pivoted column header column need to be maintained. but your table 1 does not contains any such column.
Hence i would suggest you to review your pivot query and make relevant change to your table 1 or table 1 data

Make row to column on SQL Server

2 Answers2