qry to show data in diffrent format

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

qry to show data in diffrent format

Author

Topic

pnpsql
Posting Yak Master

246 Posts

Posted - 2013-01-18 : 02:47:41

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

52326 Posts

Posted - 2013-01-18 : 03:11:05

[code]
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
[/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-18 : 03:11:39

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/

pnpsql
Posting Yak Master

246 Posts

Posted - 2013-01-18 : 05:39:43

vishak , your query shows repeated values for id column.

challenge everything

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-18 : 06:07:59

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/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-18 : 06:23:33

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/

pnpsql
Posting Yak Master

246 Posts

Posted - 2013-01-21 : 09:33:50

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources