Hi,

I am working on a small dataset and I think the output I am expecting needs PIVOT

Col1 Name
1    A
2    A
3    A
1    B
4    B
5    C


I need an output as

Name Col1 col2 col3 col4 col5 col6
A     1   2     3    NULL  NULL  NULL
B     1   4     NULL  NULL  NULL  NULL
C     5   NULL  NULL  NULL  NULL  NULL

there can be n number of columns may till col 90
[ dynamic ]
please guide

CREATE TABLE Foo (
  foo_type CHAR(1) PRIMARY KEY,
  foo_value INT);

INSERT INTO Foo VALUES('A', 1);
INSERT INTO Foo VALUES('B', 2);
INSERT INTO Foo VALUES('C', 3);
INSERT INTO Foo VALUES('D', 4);

-- pivot using CASE
SELECT MAX(CASE WHEN foo_type = 'A' THEN foo_value END) AS A,
        MAX(CASE WHEN foo_type = 'B' THEN foo_value END) AS B,
        MAX(CASE WHEN foo_type = 'C' THEN foo_value END) AS C,
        MAX(CASE WHEN foo_type = 'D' THEN foo_value END) AS D
FROM Foo;

-- pivot using PIVOT operator (SQL Server 2005/2008)
SELECT A, B, C, D
FROM Foo
PIVOT
(MAX(foo_value) FOR foo_type IN (A, B, C, D)) AS P;

-- dynamic pivot (SQL Server 2005/2008)
DECLARE @pivot_cols NVARCHAR(1000);
SELECT @pivot_cols =
        STUFF((SELECT DISTINCT '],[' + foo_type
               FROM Foo
               ORDER BY '],[' + foo_type
               FOR XML PATH('')
               ), 1, 2, '') + ']';

DECLARE @pivot_query NVARCHAR(2000);
SET @pivot_query =
N'SELECT ' + @pivot_cols +
  'FROM Foo ' +
  'PIVOT ' +
  '(MAX(foo_value) FOR foo_type IN (' + @pivot_cols + ')) AS P;';

EXEC(@pivot_query);

DROP TABLE Foo;