Piovt table - 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

Piovt table

Author

Topic

ranganaath
Starting Member

18 Posts

Posted - 2011-02-03 : 16:01:15

Posted - 02/01/2011 : 16:26:56
My table is
depot : party : product : MainGroup : SubGroup : Qty
AAA : ABC : Cable : 1 : Electric : 1
AAA : ABC : M Board : 1 : Electronic : 1
AAA : ABC : Pipe : 2 : Plastic : 1
AAA : ABC : Bricks : 2 : Civil : 1
AAA : XYZ : Cable : 1 : Electric : 2
AAA : XYZ : M Board : 1 : Electronic : 2
AAA : XYZ : Bricks : 2 : Civil : 2

please any body help in getting the following out put in sql2005
Depot Party Electric Electronic Total Plastic Civil Total Grand Total
AAA ABC 1 1 2 1 1 2 4
AAA XYZ 2 2 4 0 2 2 6

ranganath

ranganaath
Starting Member

18 Posts

Posted - 2011-02-03 : 16:03:26

i want maingroup in columns and i want total for each SubGroup

ranganath

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-03 : 16:43:14

Don't need a pivot, use Case When

Select SUM(Case When MainGroup =1 then 1 else 0 end) as Group1, SUM(Case When Maingroup=2 then 1 else 0 end) as Group2...etc

Poor planning on your part does not constitute an emergency on my part.

ranganaath
Starting Member

18 Posts

Posted - 2011-02-05 : 13:26:44

Thank sir you for your reply

i want also want subgroup as column

ranganath

ranganaath
Starting Member

18 Posts

Posted - 2011-02-05 : 13:30:43

i want total electric(subgroup) items sold to that party in column and total electronics(subgroup) goods sold in column and total of electric+electronics(Maingroup) goods sold in column.

ranganath

ranganaath
Starting Member

18 Posts

Posted - 2011-02-05 : 14:04:47

Thank you very much i have got the out put using case

Thank you once gain

quote:
Originally posted by dataguru1971

Don't need a pivot, use Case When

Select SUM(Case When MainGroup =1 then 1 else 0 end) as Group1, SUM(Case When Maingroup=2 then 1 else 0 end) as Group2...etc

Poor planning on your part does not constitute an emergency on my part.

ranganath

ranganaath
Starting Member

18 Posts

Posted - 2011-02-05 : 14:16:58

is there any way to use CASE dynamically since i am hard coding SUBGROUP and MAINGROUP

ranganath

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-05 : 16:04:37

Can you clarify exactly what that is supposed to mean?

Poor planning on your part does not constitute an emergency on my part.

ranganaath
Starting Member

18 Posts

Posted - 2011-02-05 : 16:17:43

thank you

e.g SUM(Case When subgroup ='Electrical' then Qty else 0 end) as Electrical,SUM(Case When subgroup='Electronics' ......
here i am hardcoding 'Electrical' there are so many subgroups like 'electronics', 'Civil','Mechanical'
i am having master table containing MainGroup,Subgroup using this i want to bring all the subgroups in Case Statement

ranganath

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-05 : 17:36:07

That would required dynamic SQL

I use the spt_values table to demonstrate a possible way to generate this dynamically...run this exactly and you will see how it looks.

In practice, you wouldn't need the +char(13)+ which is just so it prints more readable.


Declare @foo table (Maingroup char(1) not null,Subgroup char(1) not null)

INSERT INTO @Foo

Select CHAR(number),Char(number+8)
FROM master.dbo.spt_values
Where number between 65 and 75 and type = 'p'


DECLARE @SQL varchar(max)
SELECT @SQL =''+ COALESCE(@SQL,'') +
'SUM(Case When Maingroup = ' + char(39) + Maingroup + char(39) + ' THEN 1 else 0 end) as Group'+ Maingroup + ',' + char(13) +
'SUM(Case When Subgroup = ' + char(39) + Subgroup + char(39) + ' THEN 1 else 0 end) as SubGroup'+ Subgroup+ ',' + char(13)

FROM @foo

SELECT @SQL = 'SELECT depot,party,' + char(13) + LEFT(@SQL,len(@SQL)-2)
 + char(13) +' FROM TABLENAME GROUP BY depot,party'
 
PRINT @SQL
--EXEC (@SQL)

You could create the string dynamically, then execute it using EXEC(@SQL)

Should get you started...

Poor planning on your part does not constitute an emergency on my part.

ranganaath
Starting Member

18 Posts

Posted - 2011-02-06 : 10:13:34

Thank you sir i will work using your solution i have done the same thing using VB.NET Code

Sir i need one more help
my table is
Party---ProductName---Pack1---Pack2---Pack3
ABC-----Prod1---------1-------10------1
ABC-----Prod2---------1-------20------10
XYZ-----Prod1---------0-------100-----0
XYZ-----Prod2---------20------90------10

want like this

----------------Prod1-----------------Prod2----------.....Prod3.......
--------Pack1---Pack2---Pack3---Pack1---Pack2---Pack3
ABC-----1-------10------1-------1-------20------10
XYZ-----0-------100-----0-------20------90------10

even if Prod1-Pack1,Prod1-Pack2,Prod1-Pack3,Prod2-Pack1....... is also ok for me

Thank you in advance

ranganath

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-06 : 11:04:54

Use what you have learned and see how far you can get.

Poor planning on your part does not constitute an emergency on my part.

ranganaath
Starting Member

18 Posts

Posted - 2011-02-06 : 15:01:40

i have tried using
case when ProductName='Prod1' then
case when Pack1>0 then sum(Pack1) else 0 end else 0 end as 'Prod1-Pack1',
case when ProductName='Prod1' then
case when Pack2>0 then sum(Pack2) else 0 end else 0 end as 'Prod1-Pack2', so on
i am getting multiple rows for one party, i want one row
i have grouped ProductName,Pack1,Pack2,Pack3
is there any problem with grouping

ranganath

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-06 : 15:04:21

you haven't applied anything I showed you?

If Pack1 is a number filed, just use SUM(Pack1), for those examples.

Select ProductName,SUM(Pack1)as [Prod-Pack1]
FROM table
Group by Productname

Poor planning on your part does not constitute an emergency on my part.

ranganaath
Starting Member

18 Posts

Posted - 2011-02-06 : 15:22:03

but the Pack1,Pack2,Pack3 are columns
i want Prod1-Pack1,Prod1-Pack2,Prod1-Pack3,Prod2-Pack1,Prod3-Pack2......... in columns for each Party

using case as shown above i am getting the desired columns but i am getting no of rows=no of products

ranganath

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-06 : 16:36:40

I suggest some tutorials in basic SQL to get you more up to speed if you are going to have to do this kind of thing more regularly.

Select ProductName
,SUM(CASE When ProductName = 'Prod1' then Pack1 else 0 end)as [Prod-Pack1]
,SUM(CASE When ProductName = 'Prod2' then Pack2 else 0 end) as [Prod-Pack2]
,SUM(CASE When ProductName = 'Prod3' then Pack3 else 0 end) as [Prod-Pack3]
FROM table
Group by Productname

Poor planning on your part does not constitute an emergency on my part.

ranganaath
Starting Member

18 Posts

Posted - 2011-02-07 : 00:16:43

Thank you sir
its working

ranganath

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources