SELECT *
FROM Table t
UNPIVOT (Code FOR Name IN ([Phil],[Bill],[Larry]))u

Select distinct Date,
MAX(Case When Name = 'Phil' then Code End) OVER(PARTITION BY Date)as Phil,
MAX(Case When Name = 'Bill' then Code End) OVER(PARTITION BY Date)as Bill,
MAX(Case When Name = 'Larry' then Code End) OVER(PARTITION BY Date)as Larry
from Table

quote:

---

Originally posted by visakh16

SELECT *
FROM Table t
UNPIVOT (Code FOR Name IN ([Phil],[Bill],[Larry]))u

for making names dynamic you can use dynamic sql



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

DECLARE @table TABLE ([Date] Date,[Name] nvarchar(15),[Code] varchar(15))
INSERT INTO @table ([date],[name],[Code]) VALUES ('11/11/2012','phil','xxxx') 
INSERT INTO @table ([date],[name],[Code]) VALUES ('11/12/2012','phil','1111') 
INSERT INTO @table ([date],[name],[Code]) VALUES ('11/11/2012','Larry','yyyy') 
INSERT INTO @table ([date],[name],[Code]) VALUES ('11/12/2012','Larry','2222') 
INSERT INTO @table ([date],[name],[Code]) VALUES ('11/11/2012','Bill','zzzz') 
INSERT INTO @table ([date],[name],[Code]) VALUES ('11/12/2012','Bill','3333') 

SELECT *
FROM @table t
PIVOT
( MAX(code) FOR [Name] IN ([phil], [larry], [bill]) )u

quote:

---

Originally posted by ranalk

Thanks for the help, I have managed to Pivot.

Now I will need to put some thinking regarding dynamic pivoting for the same table...

---

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by ranalk

Thanks for the help, I have managed to Pivot.

Now I will need to put some thinking regarding dynamic pivoting for the same table...

---

see the article. it does excatly that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

CREATE  TABLE #table ([Date] Date,[Name] nvarchar(15),[Code] varchar(15))
INSERT INTO #table ([date],[name],[Code]) VALUES ('11/11/2012','phil','xxxx') 
INSERT INTO #table ([date],[name],[Code]) VALUES ('11/12/2012','phil','1111') 
INSERT INTO #table ([date],[name],[Code]) VALUES ('11/11/2012','Larry','yyyy') 
INSERT INTO #table ([date],[name],[Code]) VALUES ('11/12/2012','Larry','2222') 
INSERT INTO #table ([date],[name],[Code]) VALUES ('11/11/2012','Bill','zzzz') 
INSERT INTO #table ([date],[name],[Code]) VALUES ('11/12/2012','Bill','3333') 

DECLARE @cols varchar(1000) ='', @str varchar(max) =''
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME([NAME]) FROM #table FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
SET @str = 'SELECT [DATE], ' + @cols + ' FROM #table t 
			PIVOT
			( MAX(code) FOR [Name] IN ( ' + @cols + ' ))u '
EXEC(@str)

DROP TABLE #table

quote:

---

Originally posted by bandi

CREATE  TABLE #table ([Date] Date,[Name] nvarchar(15),[Code] varchar(15))
INSERT INTO #table ([date],[name],[Code]) VALUES ('11/11/2012','phil','xxxx') 
INSERT INTO #table ([date],[name],[Code]) VALUES ('11/12/2012','phil','1111') 
INSERT INTO #table ([date],[name],[Code]) VALUES ('11/11/2012','Larry','yyyy') 
INSERT INTO #table ([date],[name],[Code]) VALUES ('11/12/2012','Larry','2222') 
INSERT INTO #table ([date],[name],[Code]) VALUES ('11/11/2012','Bill','zzzz') 
INSERT INTO #table ([date],[name],[Code]) VALUES ('11/12/2012','Bill','3333') 

DECLARE @cols varchar(1000) ='', @str varchar(max) =''
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME([NAME]) FROM #table FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
SET @str = 'SELECT [DATE], ' + @cols + ' FROM #table t 
			PIVOT
			( MAX(code) FOR [Name] IN ( ' + @cols + ' ))u '
EXEC(@str)

DROP TABLE #table

Nicely done!
Thanks a lot.

--
Chandu

---

quote:

---

Originally posted by Lampas

--This is another way of pivoting that gives more freedom to add other value to your results, like do a calculation on the results, while you pivot.


-- Create your table
DECLARE @table TABLE ([Date] Datetime,[Name] nvarchar(15),[Code] varchar(15))
INSERT INTO @table ([date],[name],[Code]) VALUES ('11/11/2012','phil','xxxx')
INSERT INTO @table ([date],[name],[Code]) VALUES ('11/12/2012','phil','1111')
INSERT INTO @table ([date],[name],[Code]) VALUES ('11/11/2012','Larry','yyyy')
INSERT INTO @table ([date],[name],[Code]) VALUES ('11/12/2012','Larry','2222')
INSERT INTO @table ([date],[name],[Code]) VALUES ('11/11/2012','Bill','zzzz')
INSERT INTO @table ([date],[name],[Code]) VALUES ('11/12/2012','Bill','3333')

-- Create your pivot table -- note that an empty string is added against each name
-- This extra table might seem to be a lot of work, but it is a clean and fast way of coding
DECLARE @Pivotable TABLE ([Name] nvarchar(15),Phil varchar(1),Larry varchar(1),Bill varchar(1))
INSERT INTO @Pivotable ([Name],Phil,Larry,Bill) VALUES ('Phil','',NULL,NULL)
INSERT INTO @Pivotable ([Name],Phil,Larry,Bill) VALUES ('Larry',NULL,'',NULL)
INSERT INTO @Pivotable ([Name],Phil,Larry,Bill) VALUES ('Bill',NULL,NULL,'')

-- Pivot statement -- this gives more ablility to manipulate and use isnull and other functions in your statement
-- I did this in SQL2000, so it is fully backwards compatible as well.
-- The pivot statement in SQL is very effective if you need a simple pivoting only
SELECT [Date]
, Phil = Max(Code+P.Phil)
, Larry = Max(Code+P.Larry)
, Bill = Max(Code+P.Bill)
FROM @table t
join @Pivotable P on T.[Name] = P.[Name]
group by [Date]

---