I Want To Control The Grouping I Have The Data Where The Grouping Column Is More Than One How Do I Do That?

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

“I’m confused by how data is grouped when using the PIVOT operator. Can I control grouping?”

i want to control the grouping i have the data where the grouping column is more than one how do i do that?
desired out put=
COMP 500 300 100 30 10 3 ------->header column
7977797 19.67 -3.99 -7.92 -27.3 -145.2 -86.26

7977797 17.68 -7.68 -7.70 -33.07 -40.45 -58.82

actual data is as follows

SQL

COMP     500     300     100       30      10      3
7977797 19.67    NULL    NULL    NULL    NULL    NULL
7977797 NULL    -3.99    NULL    NULL    NULL    NULL
7977797 NULL    NULL    -7.92    NULL    NULL    NULL
7977797 NULL    NULL    NULL    -27.3   NULL    NULL
7977797 NULL    NULL    NULL    NULL    -145.2  NULL
7977797 NULL    NULL    NULL    NULL    NULL    -86.26
7977797 17.68    NULL    NULL    NULL    NULL    NULL
7977797 NULL    -7.68    NULL    NULL    NULL    NULL
7977797 NULL    NULL    -7.70    NULL    NULL    NULL
7977797 NULL    NULL    NULL    -33.07   NULL    NULL
7977797 NULL    NULL    NULL    NULL    -40.45   NULL
7977797 NULL    NULL    NULL    NULL    NULL    -58.82

Posted 27-Sep-15 2:47am

Member 11819086

Add a Solution

Comments

Wendelius 27-Sep-15 8:59am

If I understand your question correctly, this is a result from a PIVOT query. If that is true, what is the query you use?

Member 11819086 27-Sep-15 9:07am

my table contains more than one grouping of COMP for the pivoting columns ie for each one comp values have two times values 500,300,100,30..as i specified in my questionn

Wendelius 27-Sep-15 9:16am

So if I understand correctly, the data in your question is taken from a table so it's not a result set for a PIVOT query?

Member 11819086 27-Sep-15 10:20am

Dear Mika wendelius thank you for your suggetions will you please make the COL1 for this following querry

?????

SELECT *
FROM
(
SELECT [COMPOUND_NAME]
,CON_VALUE,
VALUE

FROM [AFileStorageDB].[dbo].[View_1]
)as s
PIVOT
(
MIN(VALUE)
FOR CON_VALUE IN ([500],[300],[100],[30],[10],[3],[1],[0.3],[0.1],[0.03],[0.01])
)AS p
ORDER BY COMPOUND_NAME

Wendelius 27-Sep-15 10:39am

You need to show what is the definition and content of view_1 and what is the logic you want to apply. You're the only one who knows the requirements and the data.

Please use "Improve question" link to add relevant information to the question.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2015-09-27T03:30:00

If I understand your question correctly the example data in your question is stored in a table, and it's not a result from a PIVOT query. If that is the case, the first thing to understand is that the rows are in no particular order in a table. In other words, in order to group them you would need to have a column or an expression that would be common for the following rows

COMP     500     300     100       30      10      3
7977797 19.67    NULL    NULL    NULL     NULL    NULL
7977797 NULL    -3.99    NULL    NULL     NULL    NULL
7977797 NULL    NULL    -7.92    NULL     NULL    NULL
7977797 NULL    NULL    NULL     -27.3    NULL    NULL
7977797 NULL    NULL    NULL     NULL     -145.2  NULL
7977797 NULL    NULL    NULL     NULL     NULL    -86.26

and be common for the following rows but different from the previous group

COMP     500     300     100       30      10      3
7977797 17.68    NULL    NULL    NULL     NULL     NULL
7977797 NULL    -7.68    NULL    NULL     NULL     NULL
7977797 NULL    NULL    -7.70    NULL     NULL     NULL
7977797 NULL    NULL    NULL     -33.07   NULL     NULL
7977797 NULL    NULL    NULL     NULL     -40.45   NULL
7977797 NULL    NULL    NULL     NULL     NULL    -58.82

As far as I can see there is no such column in the data so it's impossible to say if for example the second row belongs to the first or the second group, again remember that the order of the rows in your data cannot be used for grouping.

If the data would contain a column like the following col1

COMP    Col1  500     300     100       30      10      3
7977797 1     19.67   NULL    NULL    NULL     NULL    NULL
7977797 1     NULL    -3.99   NULL    NULL     NULL    NULL
7977797 1     NULL    NULL    -7.92    NULL    NULL    NULL
7977797 1     NULL    NULL    NULL    -27.3    NULL    NULL
7977797 1     NULL    NULL    NULL    NULL    -145.2   NULL
7977797 1     NULL    NULL    NULL    NULL     NULL    -86.26
7977797 2     17.68    NULL   NULL    NULL     NULL    NULL
7977797 2     NULL    -7.68   NULL    NULL     NULL    NULL
7977797 2     NULL    NULL    -7.70    NULL    NULL    NULL
7977797 2     NULL    NULL    NULL    -33.07   NULL    NULL
7977797 2     NULL    NULL    NULL    NULL     -40.45  NULL
7977797 2     NULL    NULL    NULL    NULL     NULL    -58.82

Then you could group the data by col1 and you would get the desired result.

Having that said, you would either need to define a new column which defines the group in one way or another or use an expression from existing data to do the grouping