2

I'm using SQL Server 2008 R2, I have this simple table

enter image description here

What I was trying to do is make a selection from this table and get this following result 

x |      1     |       2     |      3
--+------------+-------------+------------
1 |   first 1  |    first 2  |    first 3
2 |   Second 1 |    second 2 |    second 3

I thought that can be done with PIVOT

I don't know much about PIVOT AND all my search result found using PIVOT with Count() . SUM(), AVG() which will not work in my table since I'm trying to PIVOT on a varchar column

Question am I using the right function? Or is there something else I need to know to solve this issue? Any help will be appreciated

I tried this with no luck 

PIVOT(count(x) FOR value IN ([1],[2],[3]) )as total 
PIVOT(count(y) FOR value IN ([1],[2],[3]) )as total  // This one is the nearest 
of what i wand  but instead of the column value values i get 0

Here is the query if any one to test it

CREATE TABLE #test (x int , y int , value Varchar(50))
INSERT INTO #test VALUES(1,51,'first 1')
INSERT INTO #test VALUES(1,52,'first 2')
INSERT INTO #test VALUES(1,53,'first 3')
INSERT INTO #test VALUES(2,51,'Second 1')
INSERT INTO #test VALUES(2,52,'Second 2')
INSERT INTO #test VALUES(2,53,'Second 3')
SELECT * FROM #test
  PIVOT(count(y) FOR value IN ([1],[2],[3]) )as total 
 DROP TABLE #test
Taryn
  • 242,637
  • 56
  • 362
  • 405
Mina Gabriel
  • 23,150
  • 26
  • 96
  • 124
  • are you trying to return this - [SQL DEMO](http://sqlfiddle.com/#!3/634c8/8) – rs. Jul 19 '13 at 19:02
  • 1
    I smell a new question who's answer involves dynamic sql. – SQLMason Jul 19 '13 at 19:04
  • @MinaGabriel See my edit, it includes both dynamic sql and if the values are known. – Taryn Jul 19 '13 at 19:10
  • 1
    I had a couple queries like this and nobody could figure out why I made them dynamic sql... until they solved the problem themselves. One of those, "I'll rewrite this and it will be 100 times better" and when you're done, it's the same. – SQLMason Jul 19 '13 at 19:37

5 Answers5

8

When you are using the PIVOT function the values inside the IN clause need to match a value that you are selecting. Your current data does not include 1, 2, or 3. You can use row_number() to assign a value for each x:

select x, [1], [2], [3]
from
(
  select x, value,
    row_number() over(partition by x order by y) rn
  from test
) d
pivot
(
  max(value)
  for rn in ([1], [2], [3])
) piv;

See SQL Fiddle with Demo. If you then have a unknown number of values for each x, then you will want to use dynamic SQL:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(row_number() over(partition by x order by y)) 
                    from test
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT x,' + @cols + ' 
            from 
            (
              select x, value,
                row_number() over(partition by x order by y) rn
              from test
            ) x
            pivot 
            (
                max(value)
                for rn in (' + @cols + ')
            ) p '

execute(@query);

See SQL Fiddle with Demo

Taryn
  • 242,637
  • 56
  • 362
  • 405
2

Key is to use the Max function for text fields.

Query:

SELECT X, [51] [1], [52] [2], [53] [3]
FROM (select * from test) t
  PIVOT(max(Value) FOR Y IN ([51], [52], [53]) )as total

Working demo

Vasanth
  • 1,670
  • 1
  • 13
  • 17
1

You say value IN ([1],[2],[3]). This means "match if value is exactly equal to 1, 2 or 3". But in your table it never is. Something is not right there.

usr
  • 168,620
  • 35
  • 240
  • 369
1
SELECT *
FROM #test
 PIVOT(MAX(value) FOR y IN ([51],[52],[53]) )as total
EricZ
  • 6,065
  • 1
  • 30
  • 30
1

I give you a trick but it hasn't meaning.

SELECT * FROM
(SELECT x, y-50 as y, value FROM test) src
  PIVOT(max(value) FOR y IN ([1],[2],[3]) )as total
Hamlet Hakobyan
  • 32,965
  • 6
  • 52
  • 68