Toad World® Forums

TOAD, SQL SERVER, transpose data

Hi,

I am looking for a way to transpose data.

I would imagine the syntax would happen in the SELECT statement somewhere, prior to creating a table?

Using this simple query below as an example I would like this normal output

ID Code Description Count of Code
30884766 S9470 Nutritional counseling, dietitian visit 18
30884766 S9460 Diabetic management program, nurse visit 12
30884766 H2035 Alcohol and/or other drug treatment program 6

To look like this with one row by ID and each of the other columns to follow:

ID Code Description1 Count of Code 1 Code2 Description2 Count of Code 2 Code3 Description3 Count of Code 3
30884766

Here is the basic script to create the normal output:

SELECT a.ID as ID
,a.Code as Code
,w.Description as Description
,COUNT (a.Code) as “Count of Code”
INTO RiskAdjust_Temp.dbo.WHP_CPP_PROF

FROM RiskAdjust_BHI.dbo.PROFOTHER a

INNER JOIN RiskAdjust_Temp.dbo.WHP_CPT_HCPCS w
on a.Code = w.code

INNER JOIN RiskAdjust_BHI.dbo.MEMBER_QUALIFIED_CLAIMS_PROFOTHER d
on a.Id = d.ID

WHERE a.Code in (‘H0002’,‘H0012’,‘H0013’,‘H2001’,‘H2035’,‘H2036’,‘S0315’,‘S0316’,‘S0317’,‘S0320’,‘S0340’,‘S0341’,
‘S0342’,‘S9140’,‘S9141’,‘S9455’,‘S9460’,‘S9465’,‘S9470’,‘S9472’,‘S9473’,‘S9476’,‘T1023’,‘4242F’,
‘4500F’,‘H0018’,‘H0028’,‘H0037’)
and d.RISK_YEAR = 2012

GROUP BY a.ID, a.Code, w.Description;

Thank you for any advice

WHP

SQL Server 2005/2008+ (IIRC) has a pivot function. I haven’t used it myself, so I’m not sure how it works, but it might be something to look into. I haven’t used SQL Server much past 2000, before the pivot function was added.

Why not just use the Pivot Grid in TDP? There is a pivot tab on every result set or there is also a full pivot window with reporting feature on the send to menu of each grid.

Debbie

Thank you Debbie, I will try to figure that option out.

WHP

Debbie, I tried this but nothing happened, see screen shot.

Thanks

WHP
Doc1.docx (218 KB)

You dragged all of your columns into the Filter area, which is the very top line. You need to drag one column into the row area, one into the column area and then one into the data area. (Or at a minimum row and data).

Debbie

Thank you Debbie, I will try to figure this out, do not see those type of options, just one box comes up as you see in my screen shot?
WHP

See the attached screenshots. Let’s just do something simple. Drag a column to the Row area first. This will represent the grouping types. Then drag the value you want to summarize to the Data Area. The default aggregate function is Sum of values. Change this to the type of summary you want, county, sum, max, etc.

Once this works for you and makes sense then you can add columns.

Debbie
Aggreg.png

See the attached screenshots. Let's just do something simple. Drag a column to the Row area first. This will represent the grouping types. Then drag the value you want to summarize to the Data Area. The default aggregate function is Sum of values. Change this to the type of summary you want, county, sum, max, etc.

Once this works for you and makes sense then you can add columns.

Debbie

I see what you mean now Debbie, im good.
Thank you

WHP