Pivot Table - Simple Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Pivot Table - Simple Query

Author

Topic

hariharans87
Starting Member

6 Posts

Posted - 2011-07-05 : 09:01:38

Hi,
I am new to SQL. Please suggest for query.

tblData:
id data tag
1 Hariharan 1
2 Ram 1
1 24 2
2 23 2
1 Sivakasi 3
2 Sattur 3
-----------
tblHead:
Tag HeadName
1 Name
2 Age
3 City
------------------

Result:
Name Age City
Hariharan 24 Sivakasi
Ramu 23 Sattur

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-05 : 09:09:11

select t.id
,Name = max(case when h.HeadName = 'Name' then t.data else '' end)
,Age = max(case when h.HeadName = 'Age' then t.data else '' end)
,City = max(case when h.HeadName = 'City' then t.data else '' end)
from tblData t
join tblHead h
on t.tag = h.Tag
group by t.id

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-05 : 13:09:32

or even PIVOT will serve purpose if we're using sql 2005 or above.

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

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-05 : 14:05:47

Think the OP wanted some help on how to do it not just a restatement of the question :).

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-05 : 14:12:09

I was not restating the question but was suggesting use of PIVOT statement


SELECT [Name],[Age],[City]
FROM
(
SELECT t.ID,t.Data,h.HeadName
FROM tblData t
INNER JOIN tblHead h
ON h.Tag= t.Tag
)p
PIVOT (MAX(data) FOR HeadName IN ([Name],[Age],[City]))q

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

hariharans87
Starting Member

6 Posts

Posted - 2011-07-06 : 01:41:31

Thanks for all reply.
Yours SQL Queries are working fine.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-06 : 13:29:04

wc

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources