Click here to Skip to main content
15,878,959 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello experts,

I have a table called TABLEA like below

C#
ID Name	YTDLVL1	YTDLVL2	YTDLVL3	QLVL1MT	QLVL1SC	QLVL1SP	QLVL2MT	QLVL2SC	QLVL2SP	QLVL2EN
1 John	100	60	90	85				20		
1 John	100	60	90		79					90
1 John	100	60	90			55			66	



Now I am trying insert values into Table2 using sql query that gives the result like below

C#
ID      NAME	Program	Category Marks
1	John	LVL1	YTD	100
1	John	LVL1	MT	85
1	John	LVL1	SC	79
1	John	LVL1	SP	55
1	John	LVL2	YTD	60
1	John	LVL2	MT	
1	John	LVL2	SC	20
1	John	LVL2	SP	66
1	John	LVL2	EN	90
1	John	LVL3	YTD	85


What I have tried:

I have used case statement but case is checking the value in column not the column name.
When Column name = "QLVL1MT" , I want to insert two values like Program as LVL1 and Category as MT (Mathematics)
I searched all over internet but could find any resource that does this kind of job. if column value is nothing it should enter as '0'

Any help is much appreciated.
Posted
Updated 21-Oct-16 9:43am
Comments
Suvendu Shekhar Giri 21-Oct-16 14:23pm    
so, where is the query?

1 solution

You need to unpivot[^] data!

Check this:
SQL
DECLARE @tablea TABLE(
	ID INT, [Name] VARCHAR(30),
	YTDLVL1 INT, YTDLVL2 INT, 
	YTDLVL3 INT, QLVL1MT INT, 
	QLVL1SC INT, QLVL1SP INT, 
	QLVL2MT INT, QLVL2SC INT, 
	QLVL2SP INT, QLVL2EN INT)

INSERT INTO @tablea (ID, [Name], YTDLVL1, YTDLVL2, YTDLVL3, QLVL1MT, QLVL1SC, QLVL1SP, QLVL2MT, QLVL2SC, QLVL2SP, QLVL2EN)
VALUES(1, 'John', 100, 60, 90, 85, NULL, NULL, NULL, NULL, 20, NULL),
(1, 'John', 100, 60, 90, 79, NULL, NULL, NULL, NULL, 90, NULL),
(1, 'John', 100, 60, 90, NULL, NULL, 55, NULL, 66, NULL, NULL)


SELECT ID, [NAME], CASE
		WHEN Program Like '%[1]%' THEN 'LVL1'
		WHEN Program Like '%[2]%' THEN 'LVL2'
		WHEN Program Like '%[3]%' THEN 'LVL3'
		END AS Program,
	 CASE
		WHEN Program Like 'YTD%' THEN 'YTD'
		WHEN Program Like '%MT' THEN 'MT'
		WHEN Program Like '%SP' THEN 'SP'
		WHEN Program Like '%SC' THEN 'SC'
		END AS Category, Marks
FROM (
	SELECT *
	FROM @tablea 
	) AS a
UNPIVOT (Marks FOR Program IN (YTDLVL1, YTDLVL2, YTDLVL3, QLVL1MT, QLVL1SC, QLVL1SP, QLVL2MT, QLVL2SC, QLVL2SP, QLVL2EN)) AS unpvt


Reslut:
C#
ID	NAME	Program	Category	Marks
1	John	LVL1	YTD			100
1	John	LVL2	YTD			60
1	John	LVL3	YTD			90
1	John	LVL1	MT			85
1	John	LVL2	SP			20
1	John	LVL1	YTD			100
1	John	LVL2	YTD			60
1	John	LVL3	YTD			90
1	John	LVL1	MT			79
1	John	LVL2	SP			90
1	John	LVL1	YTD			100
1	John	LVL2	YTD			60
1	John	LVL3	YTD			90
1	John	LVL1	SP			55
1	John	LVL2	SC			66


I hope that is what you wanted to achieve.

For further details, please see: Unpivot Transformation[^]
 
Share this answer
 
Comments
sudevsu 24-Oct-16 9:40am    
Thanks Maciej. I did run your three statements before testing them into my query. But I get error like Program column is not found, marks is not found. Meaning create the table and insert statement doesn't have these columns right!? I am talking about these

CASE
WHEN Program Like '%[1]%' THEN 'LVL1'
WHEN Program Like '%[2]%' THEN 'LVL2'
WHEN Program Like '%[3]%' THEN 'LVL3'
END AS Program,
CASE
WHEN Program Like 'YTD%' THEN 'YTD'
WHEN Program Like '%MT' THEN 'MT'
WHEN Program Like '%SP' THEN 'SP'
WHEN Program Like '%SC' THEN 'SC'
END AS Category,

in your select.
sudevsu 9-Nov-16 9:26am    
Thanks Maciej ... It helped after working on it for a while. Thanks for the guidance
Maciej Los 9-Nov-16 11:02am    
You're very welcome. Can you accept my answer as a solution (green button) - formally, to remove your question from unanswered list.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900