How to convert columns to rows in ms SQL server

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

, +

I have one table

TIMETABLE

it contains vales as

id| Day | p1 | p2 | p3 | p4| Short_By_Name_by_Class 
1 | mon | ES | GS | CP | GN| name1
2 | tue | AB | AC | AD | AE| name2
3 | Wed | BA | BB | BC | BD| name3

Now how to convert this like

PD |mon| tue| wed 
P1 |ES | AB | BA | 
P2 |GS | AC | BB | 
P3 |CP | AD | BC | 
P4 |GN | AE | BD |

I am using this query

What I have tried:

SELECT [DAY], [MON],[TUES],[WED]FROM TIMETABLE WHERE Short_By_Name_by_Class='name1'
unpivot
(ID 
 for [DAY] in ([MON],[TUES],[WED])
 ) unpiv;

Posted 8-Feb-19 5:13am

Member 12245539

Updated 9-Feb-19 7:42am

v2

Add a Solution

Comments

CHill60 8-Feb-19 12:10pm

What is wrong with your query? What happens (or doesn't happen)?

Member 12245539 8-Feb-19 12:11pm

It shows.

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'unpivot'.

CHill60 8-Feb-19 12:21pm

So correct the syntax of the query - here is the documentation Using PIVOT and UNPIVOT - SQL Server | Microsoft Docs[^]

Member 12245539 8-Feb-19 12:22pm

plz help I have already gone through this link...

MadMyche 8-Feb-19 13:50pm

Well, you should go through it again; and slowly migrate the sample given into what you need, column by column, until you get what you need.

RedDk 8-Feb-19 17:54pm

Cutting to the chase, see here:
https://www.codeproject.com/Tips/500811/Simple-Way-To-Use-Pivot-In-SQL-Query
AND here:
https://www.codeproject.com/Tips/1011591/Dynamic-Pivot-Query-in-SQL-Server

1 solution

Add a Solution

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

Maciej Los · Accepted Answer · 2019-02-09T07:42:00

First of all, you have to unpivot[^] data {p1, p2, p3, p4} to PD and PDValue fields then pivot them again - on Day field.

SQL

SELECT PD, [mon], [tue], [wed], ...
FROM (
    SELECT id, Day, PD, PDValue 
    FROM (
            SELECT id, Day, p1, p2, p3, p4  
            FROM TIMETABLE
        ) pvtData
    UNPIVOT (PDValue FOR PD IN (p1, p2, p3, p4))
) AS unpvtData
PIVOT (PDValue FOR Day IN ([mon], [tue], [wed], ...)) AS Final

Note: not tested!

How to convert columns to rows in ms SQL server

1 solution

Solution 1

Add your solution here

Preview 0

How to convert columns to rows in ms SQL server

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us