Need the query to form the rows as column

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

SQL Server 2005 Forums

Transact-SQL (2005)

Need the query to form the rows as column

Author

Topic

kamal.A
Yak Posting Veteran

70 Posts

Posted - 2009-11-09 : 06:42:57

Hi,

I have following table.

Table name: Publishers

Pub_Name Client_Name
The Hindu AMD
The Hindu CMG
The Hindu AMD
The Hindu YUP
Indian Exp AMD
Indian Exp CMG

I need the query to obtain the following result.

Pub_Name AMD CMG YUP
The Hindu 2 1 1
Indian Exp 1 1 0

help me.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-11-09 : 06:56:29

Look up "pivot" in Books Online:

http://technet.microsoft.com/en-us/library/ms177410(SQL.90).aspx

- Lumbago
http://xkcd.com/327/

kamal.A
Yak Posting Veteran

70 Posts

Posted - 2009-11-09 : 07:04:41

Thanks, They are using two table and to retrieve the columns, but i have used only one table and client name will be added dynamically. So help me to write a query for this scenario.

thanks in advance.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-09 : 07:16:14

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

kamal.A
Yak Posting Veteran

70 Posts

Posted - 2009-11-09 : 07:23:25

Thanks for your reply. In PIVOT concept we already define the rows which are converted into columns right? now AMD, CMG, YUP are rows and this may be increase lot of clients, so how can we use the PIVOT techniques here. so pls help me.

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-11-09 : 08:07:45

CREATE TABLE TESTPIVOT(Pub_Name VARCHAR(200), Client_Name VARCHAR(100))
INSERT INTO TESTPIVOT
Select 'The Hindu', 'AMD' UNION ALL
Select 'The Hindu', 'CMG' UNION ALL
Select 'The Hindu', 'AMD' UNION ALL
Select 'The Hindu', 'YUP' UNION ALL
Select 'Indian Exp', 'AMD' UNION ALL
Select 'Indian Exp', 'CMG'

SELECT name as Pub_name,[AMD],[CMG],[YUP] FROM
(select Pub_Name as name,* from TESTPIVOT) as P
PIVOT
(COUNT(pub_name) for Client_NAme in([AMD],[CMG],[YUP]))as PVT
---------------------------------------------------------

This will help you.

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-09 : 08:39:17

quote:
Originally posted by kamal.A

Thanks for your reply. In PIVOT concept we already define the rows which are converted into columns right? now AMD, CMG, YUP are rows and this may be increase lot of clients, so how can we use the PIVOT techniques here. so pls help me.

Read the link I posted again

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources