0

I have a query that's being use in my application. I had to make a small edit to it (select an additional column) and now that I do that, I don't get the same results, therefore I get a bad file. Just to give example this is what the query looks like ....

Select 
    'X' = tblA.VendorNumber, 
    'Y' = tblB.Label, 
    'Z' = tblC.InvoiceNo, 
    'W' = tblD.Checks, 
From //Doing some joins here
Group By 
    tblA.VendorNumber, tblB.label, tblc.InvoiceNo, tblD.Checks

The result set gives me many records, but groups by the ones with identical X,Y,Z,W - so with no Group By it would look like this

X                Y                 Z                  W
-----------------------------------------------------------
123              Anton             772                0
123              Anton             772                0

Obviously, with the group by they are rolled up into one...

The issue comes when I try to include an additional column in my Select query. I need this query in my, because I need to value in my code to be able to distinguish what type of record it is. With the new column, these two rows of data are not the same, therefore they do not get rolled up.

Is there a way for me to somehow add an additional column, but not display it, and exclude it from the Group By?

This is what I mean

Select 
    'X' = tblA.VendorNumber, 
    'Y' = tblB.Label, 
    'Z' = tblC.InvoiceNo, 
    'W' = tblD.Checks, 
    'P' = tblC.Proc     -- New column
From //Doing some joins here
Group By 
    tblA.VendorNumber, tblB.label, tblc.InvoiceNo, tblD.Checks,
    tblC.Proc       -- New column

In this case the data looks like this

X                Y                 Z                  W          P
---------------------------------------------------------------------
123              Anton             772                0          FPN
123              Anton             772                0          PPN

So now that P is different for the 2 records that previously were rolled up into one, is there a way for me to somehow not display P, however, still be able to get it's value from my record set. I am unable to select the 'P' if it's not selected in this one query and because of the fact that the two records are not rolling up, I'm having some major issues.

Basically I need to select 'P' but not include it in my result set or group by.

Any help would be much appreciated.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
BobSki
  • 1,531
  • 2
  • 25
  • 61
  • 2
    If the values are different for each of the rows, and you still want them rolled up into one row, how is the processor supposed to decide which of the two different values to include in the rolled up row? No, you cannot do this in one result set. you need to perform a separate query for the other non-rolled-up result set. Or, if you ONLY want the `P` records rolled up, just add a Where clause that only rolls up the `P` rows. – Charles Bretana Aug 15 '17 at 13:32
  • Why GROUP BY when no aggregate functions are used? Are you trying to do a SELECT DISTINCT? – jarlh Aug 15 '17 at 13:44
  • What does " i need to select 'P' but not include it in my result set" even mean? What do you think is the difference between selecting something and including it in your result set? Adding your desired results from the sample data might shed some light. If you want to "get it's value from your record set", what value would you want that to be? – Tab Alleman Aug 15 '17 at 13:47

3 Answers3

3

There's a couple of options, I guess... There's not really a way to get values without having them in your results. How else would you read them?

One would be to STUFF the values of 'P' into a comma delimited list ie:

X                Y                 Z                  W          P
123              Anton             772                0          FPN,PPN

Then read them in your application separated by comma. Read here: https://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-server

Another would be to create boolean headers if there's not too many options for 'P' or you know all of the options. You can create them using CASE statements like:

,SUM(CASE WHEN tlbc.Proc = 'PPN' THEN 1 ELSE 0 END) AS "PPN"

EDIT: Left out an aggregate around it so it groups correctly. Can use MAX, for 1 or 0, as well... depends how many results there can be for tlbc.Proc. Some aggregate function around your cases will combine your rows to one.

For results like:

X                Y                 Z                  W          FPN          PPN          AnotherP
123              Anton             772                0          1          1          0

Third, if I misread your question and you don't need the values but just need them in a WHERE, then don't display them.

There's definitely more ways to go about this.

Does this help?

justiceorjustus
  • 2,017
  • 1
  • 19
  • 42
2

You need to do some sort of aggregate function on P so you don't need to group on it. If you only care about one of the values, you could use MIN() or MAX() to get one of them.

So, something like:

SELECT X, Y, Z, MAX(P) AS P etc....

Another way would be to use something like FOR XML PATH to pivot the values into a single value (maybe a comma separated list). There are lots of examples online of people doing this. Here is one example:

https://sqlperformance.com/2014/08/t-sql-queries/sql-server-grouped-concatenation

snow_FFFFFF
  • 3,235
  • 17
  • 29
0

If you only want one record per first DISTINCT four column, which one of the P column you want to show?

If you have an answer for this question, just add to the query

Where tblC.Proc = -->YOUR_EXPECTED_VALUE_OR_CONDITION_HERE<--
Mocas
  • 1,403
  • 13
  • 20