pivot with cast in sql server

Question

1.00/5 (1 vote)

See more:

Hi all,
I have the sample data like this

student_id  metric         score
1          raw_score        20
1          scale_score       30
2          proficiency      level1
2          scale_score      60   
3          raw_score        45
3          proficiency      level2
4          scale_score       54
4          proficiency       level2

the expected output is

studentid raw_score scale_score proficiency
1            20         30          null
2            null        60         level1
3            45          null        level2
4             null        54         level2

Thannks in advance.

Regards,
Prakash

Posted 11-Sep-13 8:43am

prakash.chakrala

Updated 11-Sep-13 8:49am

Maciej Los

v2

Add a Solution

Comments

Corporal Agarn 11-Sep-13 14:51pm

What is the problem? What is your code?

3 solutions

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)

Maciej Los · Answer 1 · 2013-09-11T09:04:00

Please, have a look:

SQL

CREATE TABLE #exdata (student_id  INT, metric VARCHAR(30), score VARCHAR(30))

INSERT INTO #exdata (student_id, metric, score)
SELECT 1, 'raw_score', '20'
UNION ALL SELECT 1, 'scale_score', '30'
UNION ALL SELECT 2, 'proficiency', 'level1'
UNION ALL SELECT 2, 'scale_score', '60'
UNION ALL SELECT 3, 'raw_score', '45'
UNION ALL SELECT 3, 'proficiency', 'level2'
UNION ALL SELECT 4, 'scale_score', '54'
UNION ALL SELECT 4, 'proficiency', 'level2'

DECLARE @cols VARCHAR(300)
DECLARE @dt VARCHAR(2000)
DECLARE @pt VARCHAR(MAX)

SET @cols = STUFF((SELECT DISTINCT '],[' + metric
            FROM #exdata
            ORDER BY '],[' + metric
            FOR XML PATH('')),1,2,'') + ']'

--SELECT @cols As cols

SET @dt = N'SELECT *
        FROM #exdata'
--EXEC(@dt)

SET @pt = N'SELECT student_id, ' + @cols + ' ' +
            'FROM(' + @dt + ') AS DT ' +
            'PIVOT(MAX(score) FOR metric IN(' + @cols + ')) AS PT'
EXEC(@pt)

DROP TABLE #exdata

Result:

student_id	proficiency	raw_score	scale_score
1		NULL		20		30
2		level1		NULL		60
3		level2		45		NULL
4		level2		NULL		54

thatraja · Answer 2 · 2013-09-12T03:31:00

Solution 2

Check these

Using PIVOT and UNPIVOT[^]
Pivoting data in SQL Server - (Alternate ways)[^]

Posted 12-Sep-13 3:31am

thatraja

Comments

Maciej Los 12-Sep-13 10:24am

+5!
Have you got LinkedIn account?

thatraja 12-Sep-13 10:29am

yes, thatraja is id

Maciej Los 12-Sep-13 10:44am

I can't find it ;(

thatraja 12-Sep-13 10:50am

in.linkedin.com/in/thatraja

thatraja 12-Sep-13 10:51am

I forgot to include this link in my site.

VeeraAnnapureddy · Answer 3 · 2013-09-12T06:04:00

Solution 3

SQL

SELECT student_id,raw_score,scale_score,proficiency FROM
(SELECT student_id,metric,score FROM Temp123)p
PIVOT(MAX(score) FOR metric IN(raw_score,scale_score,proficiency)) AS pvt

Posted 12-Sep-13 6:04am

VeeraAnnapureddy