SQL中对材料报价单的数据权限进行操作 (如列转行 行转列的处理)

--可以直接放到查询分析器执行

--判断测试表TmpTCIQuote是否有存在 不存在则创建

if NOT exists (select * from dbo.sysobjects where id = object_id(N'dbo.TmpTCIQuote') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

BEGIN 

create table TmpTCIQuote(

CIQuotesID   int not null,             --材料报价单ID

cSubInvCode VARCHAR(100) NOT NULL,     --材料编码

cVen1       VARCHAR(50),    --材料厂编码1

cMVenName1  VARCHAR(200),    --材料厂名称1

cVen2       VARCHAR(50),    --材料厂编码2

cMVenName2  VARCHAR(200),    --材料厂名称2

cVen3       VARCHAR(50),    --材料厂编码3

cMVenName3  VARCHAR(200)    --材料厂名称3

primary key (CIQuotesID)

);

END 

--清空数据

TRUNCATE TABLE TmpTCIQuote

--插入默认数据

INSERT INTO TmpTCIQuote(CIQuotesID,cSubInvCode,cVen1,cMVenName1,cVen2,cMVenName2,cVen3,cMVenName3)

SELECT 1,'39010000098','100595','东莞利丰商标制造有限公司','100823','广州南沙经济技术开发区彩佳印花有限公司','100436','晋江市绿雨彩色印刷有限公司' UNION ALL 

SELECT 2,'39080000001','100823','广州南沙经济技术开发区彩佳印花有限公司','100595','东莞利丰商标制造有限公司',NULL,NULL UNION ALL 

SELECT 3,'39150000101','100436','晋江市绿雨彩色印刷有限公司',NULL,NULL,'100595','东莞利丰商标制造有限公司' UNION ALL 

SELECT 4,'39150000101','100436','晋江市绿雨彩色印刷有限公司',NULL,NULL,NULL,NULL

SELECT * FROM TmpTCIQuote

/*

原始数据

ID    材料编码  材料厂编码1     材料厂名称1   材料厂编码2     材料厂名称2    材料厂编码3     材料厂名称3

1 39010000098   100595 东莞利丰商标制造有限公司 100823 广州南沙经济技术开发区彩佳印花有限公司 100436 晋江市绿雨彩色印刷有限公司

2 39080000001   100823 广州南沙经济技术开发区彩佳印花有限公司 100595 东莞利丰商标制造有限公司 NULL NULL

3 39150000101   100436 晋江市绿雨彩色印刷有限公司 NULL NULL 100595 东莞利丰商标制造有限公司

4 39150000101   100436 晋江市绿雨彩色印刷有限公司 NULL NULL NULL NULL

描述：只显示对应的材料厂编码 不相关的材料厂编码用Y表示(表示对应的已经有其它材料供应商) 为空的材料厂编码用Null表示

假设只拥有材料厂编码100595的查看数据权限 其它材料厂编码不能查看 有其它材料厂编码用Y表示 空的用Null表示

最终结果如下：

ID    材料编码  材料厂编码1     材料厂名称1   材料厂编码2     材料厂名称2    材料厂编码3     材料厂名称3

1 39010000098   100595 东莞利丰商标制造有限公司   Y Y

2 39080000001    Y 100595 东莞利丰商标制造有限公司 Null

3 39150000101    Y   Null 100595 东莞利丰商标制造有限公司

*/

DECLARE @cVenValue VARCHAR(50)   --定义材料厂编码 

SET @cVenValue = '100595';       --只允许查看100595的材料厂编码

WITH T1 AS   --对三个材料厂编码进行列转行

(SELECT CIQuotesID,cSubInvCode,cVenValue,VenIndex = REPLACE(cVen,'cVen','') FROM TmpTCIQuote

UNPIVOT (cVenValue FOR cVen IN (cVen1,cVen2,cVen3)) cVen),

T2 AS  --显示包括的材料厂编码的信息

(SELECT CIQuotesID,cSubInvCode,cVenValue,VenIndex FROM T1 WHERE cVenValue = @cVenValue),

T3 AS  --显示其它材料厂编码的信息 材料厂编码用Y表示

(SELECT CIQuotesID,cSubInvCode,cVenValue = 'Y',VenIndex FROM T1 WHERE cVenValue <> @cVenValue AND ISNULL(cVenValue,'') <> ''),

T4 AS  --显示空材料厂编码的信息 材料厂编码用N表示

(SELECT CIQuotesID,cSubInvCode,cVenValue = 'N',VenIndex FROM T1 WHERE ISNULL(cVenValue,'') = ''),

T5 AS  --根据主键ID进行关联 

(SELECT t2.CIQuotesID, --材料报价单ID

        t2.cSubInvCode, --材料编码

        t2.cVenValue, --材料厂编码(显示相关的材料厂编码)

        t2.VenIndex, --材料厂编码对应的位置(显示相关的材料厂编码 如三个材料供应商 对应的1，2，3的位置)

        cVenValue2 = t3.cVenValue, --材料厂编码(显示不相关的材料厂编码)

        VenIndex2 = t3.VenIndex, --材料厂编码对应的位置(显示不相关的材料厂编码 如三个材料供应商 对应的1，2，3的位置)

        cVenValue3 = t4.cVenValue, --材料厂编码(显示空的材料厂编码)

        VenIndex3 = t4.VenIndex --材料厂编码对应的位置(显示空的材料厂编码 如三个材料供应商 对应的1，2，3的位置)

 FROM T2 

LEFT JOIN T3 ON t2.CIQuotesID = t3.CIQuotesID   --一定要Left Join 以显示包括的材料厂编码的信息的表为主

LEFT JOIN T4 ON t2.CIQuotesID = t4.CIQuotesID), --一定要Left Join 以显示包括的材料厂编码的信息的表为主

T6 AS --对包括的材料厂编码、其它的材料厂编码和空材料厂编码进行行转列的操作

(SELECT CIQuotesID, --材料报价单ID

       cSubInvCode, --材料编码

       cVenA1 = MAX(CASE WHEN VenIndex = 1 THEN cVenValue END), --材料厂编码1(显示相关的材料厂编码)

       cVenB1 = MAX(CASE WHEN VenIndex2 = 1 THEN cVenValue2 END), --材料厂编码1(显示不相关的材料厂编码)

       cVenC1 = MAX(CASE WHEN VenIndex3 = 1 THEN cVenValue3 END), --材料厂编码1(显示空的材料厂编码)

       cVenA2 = MAX(CASE WHEN VenIndex = 2 THEN cVenValue END), --材料厂编码2(显示相关的材料厂编码)

       cVenB2 = MAX(CASE WHEN VenIndex2 = 2 THEN cVenValue2 END), --材料厂编码2(显示不相关的材料厂编码)

       cVenC2 = MAX(CASE WHEN VenIndex3 = 2 THEN cVenValue3 END), --材料厂编码2(显示空的材料厂编码)

       cVenA3 = MAX(CASE WHEN VenIndex = 3 THEN cVenValue END), --材料厂编码3(显示相关的材料厂编码)

       cVenB3 = MAX(CASE WHEN VenIndex2 = 3 THEN cVenValue2 END), --材料厂编码3(显示不相关的材料厂编码)

       cVenC3 = MAX(CASE WHEN VenIndex3 = 3 THEN cVenValue3 END) --材料厂编码3(显示空的材料厂编码)

FROM T5 

GROUP BY CIQuotesID,cSubInvCode),

T7 AS 

(SELECT CIQuotesID, --材料报价单ID

cSubInvCode, --材料编码

       cVen1 = CASE     --材料厂编码(对应的材料厂编码、其它的材料厂编码和空材料厂编码 一定会有一个有存在 另外两个相关的都为空) 

WHEN LEN(ISNULL(cVenA1,'')) > LEN(ISNULL(cVenB1,'')) AND LEN(ISNULL(cVenA1,'')) > LEN(ISNULL(cVenC1,'')) THEN cVenA1

WHEN LEN(ISNULL(cVenB1,'')) > LEN(ISNULL(cVenA1,'')) AND LEN(ISNULL(cVenB1,'')) > LEN(ISNULL(cVenC1,'')) THEN cVenB1

WHEN LEN(ISNULL(cVenC1,'')) > LEN(ISNULL(cVenA1,'')) AND LEN(ISNULL(cVenC1,'')) > LEN(ISNULL(cVenB1,'')) THEN cVenC1

END,

       cVen2 = CASE --下面的Case判断就只取有值的赋给它对应的材料厂编码1,2,3本身

WHEN LEN(ISNULL(cVenA2,'')) > LEN(ISNULL(cVenB2,'')) AND LEN(ISNULL(cVenA2,'')) > LEN(ISNULL(cVenC2,'')) THEN cVenA2

WHEN LEN(ISNULL(cVenB2,'')) > LEN(ISNULL(cVenA2,'')) AND LEN(ISNULL(cVenB2,'')) > LEN(ISNULL(cVenC2,'')) THEN cVenB2

WHEN LEN(ISNULL(cVenC2,'')) > LEN(ISNULL(cVenA2,'')) AND LEN(ISNULL(cVenC2,'')) > LEN(ISNULL(cVenB2,'')) THEN cVenC2

END,

       cVen3 = CASE 

WHEN LEN(ISNULL(cVenA3,'')) > LEN(ISNULL(cVenB3,'')) AND LEN(ISNULL(cVenA3,'')) > LEN(ISNULL(cVenC3,'')) THEN cVenA3

WHEN LEN(ISNULL(cVenB3,'')) > LEN(ISNULL(cVenA3,'')) AND LEN(ISNULL(cVenB3,'')) > LEN(ISNULL(cVenC3,'')) THEN cVenB3

WHEN LEN(ISNULL(cVenC3,'')) > LEN(ISNULL(cVenA3,'')) AND LEN(ISNULL(cVenC3,'')) > LEN(ISNULL(cVenB3,'')) THEN cVenC3

END

FROM T6)  

SELECT * FROM T7

/*

最终结果如下：

ID    材料编码  材料厂编码1    材料厂编码2  材料厂编码3

1 39010000098 100595 Y Y

2 39080000001 Y 100595 NULL

3 39150000101 Y NULL 100595

*/