具有大小写的SQL查询和基于列名/非列值的合并 [英] Sql query with case and coalesce based on column name/ not column value

问题描述

您好专家，



我有一张名为TABLEA的表格，如下所示

Hello experts,

I have a table called TABLEA like below

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	

现在我正在尝试使用sql查询将值插入到Table2中如下所示

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

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

我尝试过的事情：



我使用了case语句但是case检查列中的值而不是列名。

当列名=QLVL1MT时，我想插入两个值，如Program作为LVL1和Category作为MT（数学）

我在网上搜索但是可以找到任何做这种工作的资源。如果列值为空，则应输入为'0'



非常感谢任何帮助。

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.

推荐答案

您需要 unpivot [ ^ ]数据！



检查：

You need to unpivot[^] data!

Check this:

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：

Reslut:

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

我希望这是你想要达到的目标。



详情请见： Unpivot Transformation [ ^ ]

这篇关于具有大小写的SQL查询和基于列名/非列值的合并的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！