Converting Rows to column values - Return single row

Question

I'm fairly new to SQL programming, and I'm working on a report on our financial postings.

The postings are sorted with a string combined of 1 to several dimensions. These dimensions are stored in a database, and I would like to be able to get the string seperated into columns for each of the financial lines.

The DimensionAttributeLevelValueAllView contains the string of dimensions, but it is seperated into rows

Displayvalue  
703310
5022
PEN

I have tried using a case-when structure to filter the values into columns, but then it generates seperate lines as:

Account  | Department  | Misc
703310 
         | 5022
                       | PEN

Instead of

Account  | Department  | Misc
703310   | 5022        | PEN

The query below is supposed to combine the dimensions above with the corresponding posted line, but it creates a separate line as above with the same financial amount for each line.

SELECT
    Case When B.ValueOrdinal='1' Then B.Displayvalue end as 'Account',
    case when B.ValueOrdinal='2' then B.Displayvalue end as 'Department',
    case when B.ValueOrdinal='3' then B.Displayvalue end as 'Misc',
    A.Text,
    Sum(A.reportingcurrencyamount) as 'Posted Amount',
    A.Recid

From GeneralJournalAccountEntry A
    Inner Join DimensionAttributeLevelValueAllView B on A.Ledgerdimension = B.ValueCombinationRecID
    Inner Join DIMENSIONATTRIBUTEVALUECOMBINATION C on A.Ledgerdimension = C.RecID

Where C.accountstructure in ('5637145326','5637165585')

Group by A.Recid, B.Valueordinal, B.Displayvalue, A.Text

I have considered the dynamic Pivot function, but I can't seem to figure out how to use it for this cause.

Thanks in advance

Answer 1

You are aggregating by too many fields. Try this:

SELECT MAX(Case When B.ValueOrdinal='1' Then B.Displayvalue end) as Account,
       MAX(case when B.ValueOrdinal='2' then B.Displayvalue end) as Department,
       MAX(case when B.ValueOrdinal='3' then B.Displayvalue end) as Misc,
       A.Text,
       Sum(A.reportingcurrencyamount) as PostedAmount,
       A.Recid
From GeneralJournalAccountEntry A Inner Join
     DimensionAttributeLevelValueAllView B
     on A.Ledgerdimension = B.ValueCombinationRecID Inner Join
     DIMENSIONATTRIBUTEVALUECOMBINATION C
     on A.Ledgerdimension = C.RecID
Where C.accountstructure in ('5637145326','5637165585')
Group by A.Recid,  A.Text;

I would discourage you from using single quotes for column aliases. Use single quotes only for string and date constants. In general, just try to give columns names that do not require any escape characters at all (for instance, by not putting spaces in the name).