How to pivot table which has "-" in value field

Question

0.00/5 (No votes)

See more:

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)

Posted 14-Sep-14 19:44pm

Member 11074115

Add a Solution

Comments

syed shanu 15-Sep-14 2:08am

Chk my article for the Pivot hope that will help you.in your case you can split the name by Space and create pivot.
http://www.codeproject.com/Articles/818756/Basic-SQL-Queries-for-Beginners#13

Member 11074115 15-Sep-14 2:31am

I have written below query

select Value,Language from(
select Value,Language from [dbo].[getLabelValue1]
) up PIVOT
(SUM(Value) FOR Language IN(select Language from [dbo].[getLabelValue1)) as pvt

but it is throwing error after IN. It s not allowing query. Could you please correct my query

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

syed shanu · Answer 1 · 2014-09-14T20:43:00

Hi Check this i have worked out for you.

-- First Create sample table and insert your sample data

SQL

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.

SQL

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;