如何转动具有“ - ”的表格。在价值领域 [英] How to pivot table which has "-" in value field
本文介绍了如何转动具有“ - ”的表格。在价值领域的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想转动下表
语言价值
英语文本
法国Fr-text
阿拉伯语Ar-text
我希望得到的结果是
文本Fr-text Ar-text
英语法语阿拉伯语
如何使用数据透镜并执行此操作?注意:列在值字段中有 - ...。我不想硬编码列名...如果还有两行,那么它也应该取该值,即:IN(从值中选择*)
I want to pivot the below table
Language Value
English En-text
France Fr-text
Arabic Ar-text
I want to result to be in the form
En-text Fr-text Ar-text
English France Arabic
How can I use pivot and do this? Note: Column has "-" in value field….I don’t want to hardcode column names…if there are two more rows then it should take that value also ie: IN(slect * from Value)
推荐答案
嗨检查我已经为您解决了。
- 首先创建样本表并插入样本数据
Hi Check this i have worked out for you.
-- First Create sample table and insert your sample data
CREATE TABLE [dbo].[getLabelValue1](
[Language] [varchar](20) NULL,
[Value] [varchar](20) NULL
) ON [PRIMARY]
GO
INSERT INTO [TestTB].[dbo].[getLabelValue1]
([Language]
,[Value])
VALUES
('English'
,'En-text')
INSERT INTO [TestTB].[dbo].[getLabelValue1]
([Language]
,[Value])
VALUES
('France'
,'Fr-text')
INSERT INTO [TestTB].[dbo].[getLabelValue1]
([Language]
,[Value])
VALUES
('Arabic'
,'Ar-text')
GO
现在运行此数据透视查询。
Now run this pivot query.
DECLARE @MyColumns AS NVARCHAR(MAX),
@SQLquery AS NVARCHAR(MAX)
-- here first we get all the ItemName which should be display in Columns we use this in our necxt pivot query
select @MyColumns = STUFF((SELECT ',' + QUOTENAME(Language)
from [getLabelValue1]
group by Language
order by Language
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
-- here we use the above all Item name to disoplay its price as column and row display
set @SQLquery = N'SELECT ' + @MyColumns + N' from
(
SELECT
Language,
Value as NewValues
FROM [getLabelValue1]
) x
pivot
(
MAX(NewValues)
for Language in (' + @MyColumns + N')
) p '
exec sp_executesql @SQLquery;
这篇关于如何转动具有“ - ”的表格。在价值领域的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文