如何转动具有“ - ”的表格。在价值领域 [英] 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屋！