4

I have fetched the record from table like this:

Name                 Opp                     Bid                  Pro
----------------------------------------------------------------------
Admin                 2                      NULL                 NULL
Pragnya Sonal         7                      NULL                 NULL
Priyanka Debnath      17                     NULL                 NULL
Sanjeev Sasmal        2                      NULL                 NULL
Subrajeet Sahoo       1                      NULL                 NULL
Pragnya Sonal         NULL                   2                    NULL
Pragnya Sonal         NULL                   NULL                 1

But I want to reorder this table in this format:

Admin                 2                      NULL                 NULL
Pragnya Sonal         7                      2                    1
Priyanka Debnath      17                     NULL                 NULL
Sanjeev Sasmal        2                      NULL                 NULL
Subrajeet Sahoo       1                      NULL                 NULL

Can any one help me to solve this, thank you very much in advance

  • 3
    try this SELECT Name, SUM(Opp),SUM(Bid), SUM(Pro) FROM dbo.testCount GROUP BY Name – vijayp Feb 24 '15 at 6:05
  • 1
    check this SQL Fiddle demo – vijayp Feb 24 '15 at 6:21
4

You can use grouping to get only one row per Name. If more than one row for the same Name and column have values you have to decide what to do with those values: do you want to see minimum, maximum, sum, average etc? So for example:

SELECT Name, min(Opp), min(Bid), min(Pro) FROM your_table GROUP BY Name
0

I was attempting to achieve something similar to the Ops question but with a minor difference. I was joining some tables and attempting to determine if a condition was "true" or not. Thanks to the accepted answer by @Simo Kivistö I was able to adapt for my needs.

I was getting results similar to Op. My City and States were being duplicated.

Original query:

SELECT 
    TABLE1.CITY,
    TABLE1.STATE,
    CASE WHEN TABLE2.COLUMNA IS NULL THEN 0 ELSE 1 END AS IS_TRUE,
    CASE WHEN TABLE3.COLUMNZ IS NULL THEN 0 ELSE 1 END AS IS_STUFF
FROM
    TABLE1
    LEFT JOIN TABLE2 ON TABLE2.COLUMNA = TABLE1.STATE
    LEFT JOIN TABLE3 ON TABLE3.COLUMNZ = TABLE1.STATE

Which resulted in the restuls below:

CITY                 STATE                   IS_TRUE              IS_STUFF
--------------------------------------------------------------------------
Los Angeles           CA                     0                    1
Los Angeles           CA                     1                    0
Denver                CO                     1                    0
Denver                CO                     0                    0

I resolved using MAX() like so:

SELECT 
    TABLE1.CITY,
    TABLE1.STATE,
    MAX(CASE WHEN TABLE2.COLUMNA IS NULL THEN 0 ELSE 1 END) AS IS_TRUE,
    MAX(CASE WHEN TABLE3.COLUMNZ IS NULL THEN 0 ELSE 1 END) AS IS_STUFF
FROM
    TABLE1
    LEFT JOIN TABLE2 ON TABLE2.COLUMNA = TABLE1.STATE
    LEFT JOIN TABLE3 ON TABLE3.COLUMNZ = TABLE1.STATE
GROUP BY
    TABLE1.CITY,
    TABLE1.STATE

Results:

CITY                 STATE                   IS_TRUE              IS_STUFF
--------------------------------------------------------------------------
Los Angeles           CA                     1                    1
Denver                CO                     1                    0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.