SQL how to do a pivot of one column regardless of the data

Question

I basically need to change multiple rows into one row

So basically i have the follow results

myId  
apple  
bear  
tiger

and I want to change this into apple|bear|tiger but how can I do these with a pivot not knowing what the data result is going to be? I tried

SELECT * FROM (SELECT TOP (3) myId FROM myTable) src PIVOT (max (myId ) FOR myId IN ([1], [2], [3])) piv;

but the data isnt [1], [2], [3]

The only way to do this is with dynamic sql. what platform are you using? — Hogan, Oct 08 '14 at 17:22
What @Hogan said. If you don't know the values ahead of time you can't write a `PIVOT` statement. Dynamic SQL is the only way. — Yuck, Oct 08 '14 at 17:22
@yuck well what would you suggest, i'm trying to just select the top 3 rows of a column and get them into 1 row — , Oct 08 '14 at 17:23
Are you trying to make apple/bear/tiger into field names, or just as data in known fields? — Joachim Isaksson, Oct 08 '14 at 17:23
@Mike - if using sql server, this might work (replace `,` with `|`) http://stackoverflow.com/questions/887628/convert-multiple-rows-into-one-with-comma-as-separator/1785923#1785923 — Hogan, Oct 08 '14 at 17:23
@JoachimIsaksson what i want to do is select the top 3 rows from column x and make it into 3 columns — , Oct 08 '14 at 17:26

score 4 · Accepted Answer · answered Oct 08 '14 at 17:27

4

You could use ROW_NUMBER() to number the rows, and use a manual pivot to convert them into fields;

WITH cte AS (
  SELECT myId, ROW_NUMBER() OVER (ORDER BY myId) rn FROM mytable
)
SELECT MAX(CASE WHEN rn=1 THEN myId END) field1,
       MAX(CASE WHEN rn=2 THEN myId END) field2,
       MAX(CASE WHEN rn=3 THEN myId END) field3
FROM cte;

An SQLfiddle to test with.

answered Oct 08 '14 at 17:27

Joachim Isaksson

176,943
25
281
294

2

@Yuck Yes, I assumed that from the `TOP 3 myId`, of course I may have missed some alternative :) – Joachim Isaksson Oct 08 '14 at 17:30
Why would this work, he says he does not know the field names before hand -- I guess if he does not mind the generic fieldX then it is OK – Hogan Oct 08 '14 at 17:30
@Hogan My mistake. I though that CTE syntax and the `ROW_NUMBER` function were specific to SQL Server. – Yuck Oct 08 '14 at 17:34
1

@Yuck - nope... they are both part of the SQL standard. 99 and 2002 I believe. – Hogan Oct 08 '14 at 17:36

score 3 · Answer 2 · edited Oct 08 '14 at 17:44

You need to use Dynamic SQL to construct all the distinct possible values to be used in PIVOT

In your case you can fetch the TOP 3 values and construct the column list and use PIVOT.

This Dynamic Column PIVOT article explains how to do it.

In the first select query, we are getting data values for the top 3 myid values and constructing the column list Using this @cols variable inside the dynamic PIVOT query. You need to add order by on appropriate column.

DECLARE @cols NVARCHAR(2000)
SELECT  @cols = STUFF(( SELECT DISTINCT 
                                '],[' + t1.data
                        FROM    Table1 AS t1
                        JOIN ( Select TOP 3 myId from Table1) T
                        on t1.myId = T.myId
                        ORDER BY '],[' + t1.data
                        FOR XML PATH('')
                      ), 1, 2, '') + ']'

DECLARE @query NVARCHAR(4000)
SET @query = N'SELECT myId, '+
@cols +'
FROM
(SELECT t1.myId , t1.data 
        FROM Table1 
        JOIN ( Select TOP 3 myId from Table1) T
        on t1.myId = T.myId
) p
PIVOT
(
MAX(myId)
FOR ColName IN
( '+
@cols +' )
) AS pvt
'

EXECUTE(@query)

Perhaps overly pedantic, but you should use `QUOTENAME` to escape object names, not just `'[' + Col + ']'`, also I am sure it will be optimised away, but `TOP 100 PERCENT` is redundant, you are allowed to use `ORDER BY` in a subquery where `FOR XML` is used. — GarethD, Oct 08 '14 at 17:40
@GarethD, thanks for the suggestion, removed top 100 percent. — radar, Oct 08 '14 at 17:41

SQL how to do a pivot of one column regardless of the data

2 Answers2

Linked