Help Creating Pivot Table In SQL 2005

Have you a little bit of sample data to play with ?

and the output you want is like:

        tt1    tt2    tt3    tt4    tt5....
at1    1      0      2       0     4
at2    0      3      5       4     0

and you want to use PIVOT ? Is that the spec ?

Yes, I'd like to use pivot, but I am having a terrible time.

I created a view and then a view to the view to total to get it to work - but obviously - that's not the way to go.
Here are my views:
SELECT     TOP (100) PERCENT ATTORNEY, (CASE [trade type] WHEN 'Par' THEN COALESCE (COUNT([matter number]), 0) ELSE 0 END) AS Par,
                      (CASE [trade type] WHEN 'Trade Claim' THEN COALESCE (COUNT([matter number]), 0) ELSE 0 END) AS [Trade Claim],
                      (CASE [trade type] WHEN 'Distressed' THEN COALESCE (COUNT([matter number]), 0) ELSE 0 END) AS Distressed,
                      (CASE [trade type] WHEN 'Primary' THEN COALESCE (COUNT([matter number]), 0) ELSE 0 END) AS [Primary], (CASE [trade type] WHEN NULL
                      THEN COALESCE (COUNT([matter number]), 0) ELSE 0 END) AS Blank, COUNT([MATTER NUMBER]) AS tot
FROM         dbo.MATTERS
WHERE     ([SELLER NAME] <> N'[general]') AND ([BUYER NAME] <> N'[general]') AND ([Closed Matters] <> 1) AND ([CLIENT NUMBER] <> 888) AND
                      ([CLIENT NUMBER] <> 200) AND (ATTORNEY <> 'Deleted')
GROUP BY ATTORNEY, [trade type]
HAVING      (ATTORNEY IS NOT NULL)
ORDER BY ATTORNEY

step2:
SELECT     ATTORNEY, SUM(Par) AS Par, SUM([Trade Claim]) AS [Trade Claim], SUM(Distressed) AS Distressed, SUM([Primary]) AS [Primary], SUM(Blank)
                      AS Blank, SUM(tot) AS Total
FROM         dbo.v_AttyOpenTradesStep1
GROUP BY ATTORNEY

Results:
Atty     Total      Par     Distressed   etc
DE        110       100        10
AJ        50          20          30

OK, so it is a matrix for the output more than using the PIVOT sql command - because that can be a right pain to get the column definitions working - which is probably the same problem you are having now...

How are you using this - meaning what do you want to be able to do with SQL - just select, use a stored procedure or don't particularly care as long as the output is right ?

I just need to use it as the recordsource in an access table.  A view would suffice.
I'd like to know how to do this in a pivot, but the most important criteria is that it works.
Are there any free wizards out there to create pivot tables in SQL 2005?

Not that I have come across...

If you can leave it with me for a little while, will give you the solution and a pivot version of the same, but, I do need to get a little shut eye right now - the sun is coming up and I haven't quite had my beauty sleep (down here in Australia).

Is that OK ?

That's fine - I appreciate your help.

hi, try this

create table MATTERS (ATTORNEY varchar(100), [MATTER NUMBER] int, [trade type] varchar(100))
 
insert matters values ('A1', 1, 'Par')
insert matters values ('A1', 1, 'Distressed')
insert matters values ('A1', 1, 'Distressed')
insert matters values ('A1', 1, 'Trade Claim')
insert matters values ('A1', 1, 'Trade Claim')
insert matters values ('A1', 1, 'Trade Claim')
insert matters values ('A1', 1, 'Primary')
insert matters values ('A1', 1, 'Primary')
insert matters values ('A1', 1, 'Primary')
insert matters values ('A1', 1, 'Primary')
insert matters values ('A1', 1, 'Primary')
 
insert matters values ('A2', 1, 'Par')
insert matters values ('A2', 1, 'Par')
insert matters values ('A2', 1, 'Par')
insert matters values ('A2', 1, 'Par')
insert matters values ('A2', 1, 'Par')
insert matters values ('A2', 1, 'Trade Claim')
insert matters values ('A2', 1, 'Trade Claim')
insert matters values ('A2', 1, 'Trade Claim')
insert matters values ('A2', 1, 'Trade Claim')
insert matters values ('A2', 1, 'Trade Claim')
insert matters values ('A2', 1, 'Trade Claim')
insert matters values ('A2', 1, 'Trade Claim')
insert matters values ('A2', 1, 'Primary')
insert matters values ('A2', 1, 'Primary')
 
SELECT ATTORNEY
	   ,COUNT(CASE WHEN [trade type]='Par' THEN [MATTER NUMBER] ELSE NULL END) AS [Par]
	   ,COUNT(CASE WHEN [trade type]='Trade Claim' THEN [MATTER NUMBER] ELSE NULL END) AS [Trade Claim]
	   ,COUNT(CASE WHEN [trade type]='Distressed' THEN [MATTER NUMBER] ELSE NULL END) AS [Distressed]
	   ,COUNT(CASE WHEN [trade type]='Primary' THEN [MATTER NUMBER] ELSE NULL END) AS [Primary]
       ,COUNT([MATTER NUMBER]) AS [Total]
FROM MATTERS
GROUP BY ATTORNEY
 
SELECT B.*, A.Total
FROM
(
SELECT ATTORNEY, COUNT([MATTER NUMBER]) AS Total
FROM MATTERS
GROUP BY ATTORNEY
) A
INNER JOIN
(
SELECT *
FROM
(SELECT ATTORNEY, [trade type], [MATTER NUMBER]
FROM MATTERS) AS T
PIVOT
(
COUNT([MATTER NUMBER])
FOR [trade type] IN ([Par],[Trade Claim], [Distressed], [Primary])
) AS P
) B
ON A.ATTORNEY = B.ATTORNEY

Select allOpen in new window

Thank you ee_rlee, but this returns two tables.

I think it should be something like this, but I'm having trouble getting it right:
Select TOP (100) PERCENT ATTORNEY,
                                (CASE [trade type] WHEN 'Par' THEN COALESCE (COUNT([matter number]), 0) ELSE 0 END) AS [Par],
                      (CASE [trade type] WHEN 'Trade Claim' THEN COALESCE (COUNT([matter number]), 0) ELSE 0 END) AS [Trade Claim],
                      (CASE [trade type] WHEN 'Distressed' THEN COALESCE (COUNT([matter number]), 0) ELSE 0 END) AS [Distressed],
                      (CASE [trade type] WHEN 'Primary' THEN COALESCE (COUNT([matter number]), 0) ELSE 0 END) AS [Primary],
                                (CASE [trade type] WHEN NULL THEN COALESCE (COUNT([matter number]), 0) ELSE 0 END) AS [Blank],
                                    COUNT([MATTER NUMBER]) AS tot
      FROM (                        select attorney, [trade type],count([matter number]) from
                                       dbo.MATTERS WHERE ([SELLER NAME] <> N'[general]') AND ([BUYER NAME] <> N'[general]') AND ([Closed Matters] <> 1) AND ([CLIENT NUMBER] <> 888) AND
                                      ([CLIENT NUMBER] <> 200) AND (ATTORNEY <> 'Deleted') GROUP BY ATTORNEY, [trade type]
                                          HAVING      (ATTORNEY IS NOT NULL)
)XX
PIVOT(
sum(COUNT([MATTER NUMBER]) )
FOR count([matter number]) IN ([Par],[Distressed],[Primary],[Trade Claim])
) as pvt
 
order by 1,2

@mark_wills:

Hi.

Do you know how to handle NULL values in PIVOT function? I created a workaround in my last post by changing null values to empty strings, but I hope there is a better way...

And thanks for the compliment! :-)

Thank you both!  What would I do without Experts Exchange?!

Hi, ee_rlee:

No, other than grouping as "Other" in the detail area so it creates a "catch all" type column, because there arguably shouldn't be NULL , and PIVOT should retun zero for combo's that are not represented by actual data. So, it could be argued, that [trade type] in the second query example shoul be only from the desired list and therefore isnull([trade type],' ') isn't required.

The bigger challenge I normally have is creating a dynamic list of categories for the columns such as trade type, and controlling the number of different categories to effect a page width type limit. But it can be done, just a bit yucky. But then, I would go to excel and link to the table, or, use analysis services, or use a crosstab in reporting services...

Also give you a rap in my bio. Like your work...

Most importantly, you have the answer exactly as the Author had requested.

Cheers,

Ok, thanks!