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
 General SQL Server Forums
 New to SQL Server Programming
 qry to show data in diffrent format
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pnpsql
Posting Yak Master

India
246 Posts

Posted - 01/18/2013 :  02:47:41  Show Profile  Reply with Quote

i have TABLE LIKE below

id charge amnt
1 11 33
1 12 34
1 16 98
2 11 12
2 16 12
2 12 30
2 15 25

i need a query that shows me dat LIKE below

id 11 12 15 16
1 33 34 0 98
2 12 30 25 12
pls help

challenge everything

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/18/2013 :  03:11:05  Show Profile  Reply with Quote

SELECT id,
COALESCE([11],0) AS [11],
COALESCE([12],0) AS [12],
COALESCE([15],0) AS [15],
COALESCE([16],0) AS [16]
FROM table t
PIVOT (SUM(amnt) FOR charge IN ([11],[12],[15],[16]))p



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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/18/2013 :  03:11:39  Show Profile  Reply with Quote
for dtermining pivot values dynamically use

http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

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

Go to Top of Page

pnpsql
Posting Yak Master

India
246 Posts

Posted - 01/18/2013 :  05:39:43  Show Profile  Reply with Quote
vishak , your query shows repeated values for id column.



challenge everything
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/18/2013 :  06:07:59  Show Profile  Reply with Quote
quote:
Originally posted by pnpsql

vishak , your query shows repeated values for id column.



challenge everything


what do you mean by that?
show an example please

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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/18/2013 :  06:23:33  Show Profile  Reply with Quote
see below for full illustration of sample data you posted


declare @test table
(
id int,
charge int,
amnt int
)
insert @test
values
(1, 11, 33),
(1, 12, 34), 
(1, 16, 98),
(2, 11, 12),
(2, 16, 12),
(2, 12, 30), 
(2, 15, 25)


SELECT id,
COALESCE([11],0) AS [11],
COALESCE([12],0) AS [12],
COALESCE([15],0) AS [15],
COALESCE([16],0) AS [16]
FROM @test t
PIVOT (SUM(amnt) FOR charge IN ([11],[12],[15],[16]))p



output
----------------------------------------
id	11	12	15	16
----------------------------------------
1	33	34	0	98
2	12	30	25	12


If its not working for you then i'm sure you've some other columns in table or part in query which you've not shown as so far
Unless you give us full information, we cant provide you exact solution for your scenario. So please post any additional information that's applicable to your scenario

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

Go to Top of Page

pnpsql
Posting Yak Master

India
246 Posts

Posted - 01/21/2013 :  09:33:50  Show Profile  Reply with Quote
yes it is ok, thanks but what i do when o don't know how many diffrent types of charges there and i need all as column label

challenge everything
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.06 seconds. Powered By: Snitz Forums 2000