-1

I know there are a lot of questions revolving around pivot already but I could not find anything that does something similar to what I am trying to do. It may not even be a pivot functionality.

I have a table with two core columns:

ID_no | items

They are setup like this:

enter image description here

I want to make it so that the item numbers are listed as columns in order. So basically for 000998571 I need the columns to be listed as:

23456 | 31462 | 31234 | 2 | I

and each item has its own column. I have been searching everywhere for a similar layout but I cant find anything that has worked for my situation. Any help at all is appreciated.

Edit: More clarification on the results I am trying to get:

enter image description here

Zi0n1
  • 594
  • 2
  • 5
  • 16
  • 1
    possible duplicate of [SQL Server dynamic PIVOT query?](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) – Giorgi Nakeuri Apr 03 '15 at 14:40
  • 1
    How do you want your result to look like? – Giorgi Nakeuri Apr 03 '15 at 14:42
  • I just added some more clarification for the results I am looking for. I will take a look at that article and see if it helps me out. – Zi0n1 Apr 03 '15 at 15:15
  • This is a pivot or cross tab. Personally I prefer cross tab because I find the syntax less obtuse but that is personal preference. This question has been asked and answered on this site dozens of times every single day. The link above is to a dynamic version when you don't know the number of columns. If you will always have 5 values to pivot you can do a static pivot instead. – Sean Lange Apr 03 '15 at 15:17
  • Pivot requires source data. Your question can be improved by adding a column to your source table, you could name it ItemName for example. – nshah Apr 03 '15 at 15:18
  • @nshah It has a name in the original question (items) – Sean Lange Apr 03 '15 at 15:26
  • @SeanLange, what is the source for the columns named Item1, Item2, Item3, etc (as show in the results)? It's confusing for beginners. – nshah Apr 03 '15 at 15:47
  • @nshah look at the base table it started from. There are two columns (ID_no and Items). Seems pretty straight forward to me unless I am not understanding your question. – Sean Lange Apr 03 '15 at 15:56

1 Answers1

1

Try this:

DECLARE @t TABLE
    (
      ID_NO NVARCHAR(MAX) ,
      Items NVARCHAR(MAX)
    )

INSERT  INTO @t
VALUES  ( '000998571', '23456' ),
        ( '000998571', '31462' ),
        ( '000998571', '31234' ),
        ( '000998571', '2' ),
        ( '000998571', '1' ),
        ( '000998582', '10701' ),
        ( '000998582', '414214' ),
        ( '000998582', '75404' ),
        ( '000998582', '2' ),
        ( '000998582', 'A' )


SELECT  ID_NO ,
        [1] AS Item1 ,
        [2] AS Item2 ,
        [3] AS Item3 ,
        [4] AS Item4 ,
        [5] AS Item5
FROM    ( SELECT    * ,
                    ROW_NUMBER() OVER ( PARTITION BY ID_NO ORDER BY Items ) AS rn
          FROM      @t
        ) t PIVOT( MAX(Items) FOR rn IN ( [1], [2], [3], [4], [5] ) ) p

Output:

ID_NO       Item1   Item2   Item3   Item4   Item5
000998571   1       2       23456   31234   31462
000998582   10701   2       414214  75404   A
Giorgi Nakeuri
  • 35,155
  • 8
  • 47
  • 75
  • exactly what I was looking for... I had a feeling I had to use row_number in it somewhere just wasnt sure how haha. – Zi0n1 Apr 03 '15 at 17:21