Query for this data - 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

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Query for this data

Author

Topic

iwahyudi
Starting Member

11 Posts

Posted - 2013-02-07 : 07:29:08

Hai , Newbie in sql please help me to how to result this data:
Brand_name Red Black Green blue
Honda 2 1 - -
Suzuki - - - -
Yamaha 1 - - 1
Kawasaki - - 1 -

And i have data table like this

Table_Brand

Dealer_id Brand_Name
H001 Honda
H002 Honda
Y001 Yamaha
Y002 Yamaha
K001 Kawasaki
S001 Suzuki

Table_Transaction

Dealer_id Colour
H001 Red
H001 Red
H002 Black
Y001 Red
Y002 Blue
K001 Green

Please help me

Indra Wahyudi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-07 : 09:39:52

[code]
SELECT Brand_Name,[Red],[Black],[Blue],[Green]
FROM
(
SELECT b.Brand_Name, t.Colour
FROM Table_Brand b
INNER JOIN Table_Transaction t
ON t.Dealer_id = b.Dealer_id
)r
PIVOT (COUNT(1) FOR Colour IN ([Red],[Black],[Blue],[Green]))p
[/code]

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

iwahyudi
Starting Member

11 Posts

Posted - 2013-02-07 : 23:15:07

Thx U Visakh16.....

Indra Wahyudi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-08 : 03:33:11

welcome

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-02-08 : 06:38:29

This is for dynamic number of colours http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

iwahyudi
Starting Member

11 Posts

Posted - 2013-02-14 : 04:35:46

Hai..
Sorry Need Help again

How to join this query into one table ..query like this :

select sum(ots_principal) as tot_ar from WOM_AGING.XAGING_MONTHLY where periode = 201212

select count(order_no) as tot_count from WOM_AGING.XAGING_MONTHLY where periode = 201212

select distinct b.cabang_data_audit as cabang,
(case
when a.dpd = 0 then '1 DPD 0'
when a.dpd <= 30 then '2 DPD 01-30'
when a.dpd <= 60 then '3 DPD 31-60'
when a.dpd <= 90 then '4 DPD 61-90'
when a.dpd <= 120 then '5 DPD 91-120'
when a.dpd <= 150 then '6 DPD 121-150'
when a.dpd <= 180 then '7 DPD 151-180'
when a.dpd > 180 then '8 DPD 180 up'
else '9 tdk ada data '
end )bucket

from WOM_AGING.XAGING_MONTHLY a,WOM_AUDIT.SWI_CABANG_MAP_AUDIT b
where a.cab_id =b.cab_id and a.periode= 201212
group by cabang_data_audit

Ps: for Field Cabang and Buket already succes but Tot_ar an tot_count not yet because always error message

The result must like this :

Cabang Buket Tot_Ar Tot_Count

Best Rgs
Indra

Indra Wahyudi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-14 : 10:06:35

so do you want cummulate figures for other two fields (Tot_Ar Tot_Count) without splitting based on others

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

iwahyudi
Starting Member

11 Posts

Posted - 2013-02-14 : 10:21:12

Yes Visakh16 without split

The result must like this :

Cabang Bucket Tot_Ar Tot_Count

But I success for Cabang and Bucket but other not yet

how to join that into one table??

thx

Indra Wahyudi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources