Sql查询将行转换为我需要的格式数据下面的列。 [英] Sql query to convert rows into column below format data I required.

问题描述

我有以下格式的数据 -

I have data in below format --

CompanyId	ParamKey	        ParamValue
ABC	        MinLength	           8
ABC	        MaxLength	           0
ABC	        IsAlphanumeric	           1
ABC	        IsCaseSensitive	           0
ABC	        IsSpecialChars	           0
ABC	        PasswordExpiryDays	  80
ABC	        AlertDays	          10

所需输出如下 -

Required Output Like This-

CompanyId MinLength MaxLength IsAlphanumeric IsCaseSensitive IsSpecialChars PasswordExpiryDays	AlertDays
  ABC	8	0	1	0	0	80	10

我的尝试：



我尝试过使用数据透视但无法获得所需的结果。请帮助。

What I have tried:

I have tried using pivot but can not get required result. PLease help.

推荐答案

我假设以下表结构（特别是列参数值> = 0）

并提供以下内容查询：

表：

I am assuming the following table structure (in particular that column paramvalue >= 0)
and providing the following query:
Table:

<pre>
create table companyvalues
(
companyid           varchar(5),
paramkey            varchar(30),
paramvalue          integer,
constraint pk_companyvalues primary key(companyid, paramkey),
constraint ck_paramvalue check(paramvalue >= 0)
);

with companynames as
(
select distinct companyid compid
from companyvalues
) ,
details as
(
select * from companyvalues
) 
select compid, max(MinLength) MinLength, max(MaxLength) MaxLength, max(IsAlphanumeric) IsAlphanumeric, 
max(IsCaseSensitive) IsCaseSensitive, max(IsSpecialChars) IsSpecialChars, max(PasswordExpiryDays) PasswordExpiryDays,
max(AlertDays) AlertDays
from
(
select compid 
		, case when  paramkey = 'MinLength'          then paramvalue else -1 end  MinLength         	
		, case when  paramkey = 'MaxLength'          then paramvalue else -1 end  MaxLength         	
		, case when  paramkey = 'IsAlphanumeric'     then paramvalue else -1 end  IsAlphanumeric    
		, case when  paramkey = 'IsCaseSensitive'    then paramvalue else -1 end  IsCaseSensitive   
		, case when  paramkey = 'IsSpecialChars'     then paramvalue else -1 end  IsSpecialChars    
		, case when  paramkey = 'PasswordExpiryDays' then paramvalue else -1 end  PasswordExpiryDays
		, case when  paramkey = 'AlertDays'          then paramvalue else -1 end  AlertDays         
from companynames, details
where compid = companyid
) finalresult
group by compid
order by 1

你真的尝试使用PIVOT吗？这是一个工作示例。

Did you really try using PIVOT? Here is a working example.

DECLARE @cp2 TABLE (
Companyid	VARCHAR(50),
ParamKey	VARCHAR(50),
ParamValue	INT
)

INSERT @cp2 ([CompanyId], [ParamKey], [ParamValue]) VALUES (N'ABC', N'MinLength', 8)
INSERT @cp2 ([CompanyId], [ParamKey], [ParamValue]) VALUES (N'ABC', N'MaxLength', 0)
INSERT @cp2 ([CompanyId], [ParamKey], [ParamValue]) VALUES (N'ABC', N'IsAlphanumeric', 1)
INSERT @cp2 ([CompanyId], [ParamKey], [ParamValue]) VALUES (N'ABC', N'IsCaseSensitive', 0)
INSERT @cp2 ([CompanyId], [ParamKey], [ParamValue]) VALUES (N'ABC', N'IsSpecialChars', 0)
INSERT @cp2 ([CompanyId], [ParamKey], [ParamValue]) VALUES (N'ABC', N'PasswordExpiryDays', 80)
INSERT @cp2 ([CompanyId], [ParamKey], [ParamValue]) VALUES (N'ABC', N'AlertDays', 10)
INSERT @cp2 ([CompanyId], [ParamKey], [ParamValue]) VALUES (N'XYZ', N'MinLength', 7)
INSERT @cp2 ([CompanyId], [ParamKey], [ParamValue]) VALUES (N'XYZ', N'MaxLength', 1)
INSERT @cp2 ([CompanyId], [ParamKey], [ParamValue]) VALUES (N'XYZ', N'IsAlphanumeric', 3)
INSERT @cp2 ([CompanyId], [ParamKey], [ParamValue]) VALUES (N'XYZ', N'IsCaseSensitive', 1)
INSERT @cp2 ([CompanyId], [ParamKey], [ParamValue]) VALUES (N'XYZ', N'IsSpecialChars', 1)
INSERT @cp2 ([CompanyId], [ParamKey], [ParamValue]) VALUES (N'XYZ', N'PasswordExpiryDays', 90)
INSERT @cp2 ([CompanyId], [ParamKey], [ParamValue]) VALUES (N'XYZ', N'AlertDays', 5)

SELECT Companyid, MinLength, [MaxLength], IsAlphanumeric, IsCaseSensitive, IsSpecialChars,
PasswordExpiryDays,AlertDays
FROM (
SELECT Companyid, ParamKey, ParamValue
FROM @cp2) up
PIVOT (MAX(ParamValue) FOR ParamKey IN (MinLength, [MaxLength], IsAlphanumeric, IsCaseSensitive, IsSpecialChars,
PasswordExpiryDays,AlertDays)) AS pvt
ORDER BY Companyid
GO

输出：

Output:

Companyid	MinLength	MaxLength	IsAlphanumeric	IsCaseSensitive	IsSpecialChars	PasswordExpiryDays	AlertDays
ABC	8	0	1	0	0	80	10
XYZ	7	1	3	1	1	90	5

引用：

我尝试过使用pivot

I have tried using pivot

SQL Pivot就是你所需要的。

SQL Pivot is what you need.

引用：

但无法获得所需的结果。

but can not get required result.

这不是提供信息，说明结果不是你想要的。

显示查询和实际结果。



Sql Server中的PIVOT和UNPIVOT | SqlHints.com [ ^ ]

在SQL查询中使用Pivot的简单方法 [ ^ ]

使用SQL Server中的'Pivot'将行转换为列 - 堆栈溢出 [ ^ ]

This is not informative, state in what the result is not what you want.
Show query, and actual result.

PIVOT and UNPIVOT in Sql Server | SqlHints.com[^]
Simple Way To Use Pivot In SQL Query[^]
Convert Rows to columns using 'Pivot' in SQL Server - Stack Overflow[^]

这篇关于Sql查询将行转换为我需要的格式数据下面的列。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！