SQL Report to display grouped data horizontally

Transact-SQL
1

Hi, I have sql using case statements to sum value based on account and liquidity status. I would like the data grouped by account number and name and display all calculation across one row instead of vertically by each case statement calculation. Here is my sql:

Select f.ACCT_CD, f.ACCT_NAME,
(Case when p.UDF_CHAR1 = 4 then SUM(p.MKT_VAL_SOD) else 0 end) 'Illiquid',
(Case when p.UDF_CHAR1 = 4 then SUM(p.MKT_VAL_SOD)/f.NET_ASSETS100 else 0 end) 'Illiquid %',
(Case when p.UDF_CHAR1 = 1 then SUM(p.MKT_VAL_SOD) else 0 end) 'Highly Liquid',
(Case when p.UDF_CHAR1 = 1 then SUM(p.MKT_VAL_SOD)/f.NET_ASSETS100 else 0 end) 'Highly Liquid %'
from cs_position p join CS_FUND f on convert(varchar(10),p.acct_cd) = convert(varchar(10),f.acct_cd)
where f.ACCT_CD = '8' and p.UDF_CHAR1 is not null
group by f.ACCT_CD, f.ACCT_NAME, p.UDF_CHAR1, f.NET_ASSETS

It will return data displayed in two rows like this
ACCT_CD ACCT_NAME Illiquid Illiquid % Highly Liquid Highly Liquid %
8 SCIENCE & TECH FUND 0 0 5,480,101,299 86.0836
8 SCIENCE & TECH FUND 433,060,665 6.8026 0 0

I'd like it to display like this with each calc on one row.
ACCT_CD ACCT_NAME Illiquid Illiquid % Highly Liquid Highly Liquid %
8 SCIENCE & TECH FUND 433,060,665 6.8026 5,480,101,299 86.0836

Thanks. Sorry for skewed text and thanks for any help.

2

By SQL report are you referring to SSRS or a report you generate using SQL?

3

Try this:

 Select f.ACCT_CD
      , f.ACCT_NAME
      , Illiquid = sum(Case When p.UDF_CHAR1 = 4 Then p.MKT_VAL_SOD Else 0 End)
      , IlliquidPerc = sum(Case When p.UDF_CHAR1 = 4 Then p.MKT_VAL_SOD / f.NET_ASSETS100 Else 0 End)
      , HighlyLiquid = sum(Case When p.UDF_CHAR1 = 1 Then p.MKT_VAL_SOD Else 0 End)
      , HighlyLiquidPerc = sum(Case When p.UDF_CHAR1 = 1 Then p.MKT_VAL_SOD / f.NET_ASSETS100 Else 0 End)
   From cs_position p
   Join CS_FUND f On convert(varchar(10), p.acct_cd) = convert(varchar(10), f.acct_cd)
  Where f.ACCT_CD = '8'
    And p.UDF_CHAR1 Is Not Null
  Group By
        f.ACCT_CD
      , f.ACCT_NAME
      , p.UDF_CHAR1
      , f.NET_ASSETS
2 Likes
4

Jeff, I appreciate the response, but this returned the same format. column data appears vertically instead of horizontally.

5

Not sure why you state the data appears vertically - this is a standard cross-tab style query. Instead of just stating it isn't working - can you provide sample data in the form of create and insert statements and expected results?

You can also try removing the non-used grouping columns.

group by f.ACCT_CD, f.ACCT_NAME

1 Like
6

My apologies. Removing the non-used grouping columns got the desired results. Thanks for your time and help.