SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Other SQL Server Topics (2005)
 Query for this data		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

iwahyudi
Starting Member

Indonesia
11 Posts
Posted - 02/07/2013 :  07:29:08  Show Profile  Reply with Quote
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

India
48041 Posts
Posted - 02/07/2013 :  09:39:52  Show Profile  Reply with Quote 

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

iwahyudi
Starting Member

Indonesia
11 Posts
Posted - 02/07/2013 :  23:15:07  Show Profile  Reply with Quote
Thx U Visakh16.....

Indra Wahyudi
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
48041 Posts
Posted - 02/08/2013 :  03:33:11  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22469 Posts
Posted - 02/08/2013 :  06:38:29  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

iwahyudi
Starting Member

Indonesia
11 Posts
Posted - 02/14/2013 :  04:35:46  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
48041 Posts
Posted - 02/14/2013 :  10:06:35  Show Profile  Reply with Quote
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/
Go to Top of Page

iwahyudi
Starting Member

Indonesia
11 Posts
Posted - 02/14/2013 :  10:21:12  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000