Sql: show data in a single row instead of different columns

Question

I wrote a sql query which is as below

SELECT ryot_code, ryot_name, variety_group, SUM(total_area)
FROM field_survey_trn
WHERE unit_code = '01'
  AND season_cntrl = 20
  AND crop_type_code NOT IN (11,12)
  AND ryot_code != 0
GROUP BY ryot_code, ryot_name, variety_group;

output

In variety_group column I am getting different variety code for same ryot_code in different lines.

My question is, how can I write a query, using which I can get all type or ** variety_code** for a same grower in a single row like below image

@jarlh I think he just didn't include everything. So this is just a pivot question. — Tim Biegeleisen
– Tim Biegeleisen, Commented Nov 3, 2015 at 8:02

Tim Biegeleisen · Accepted Answer · 2015-11-03 09:39:57Z

1

I wrapped the query in your original question in an outer query which performs a pivot on the variety_group column:

SELECT t.ryot_code, t.ryot_name,
    SUM(CASE WHEN t.variety_group = 10 THEN t.theSum ELSE 0 END) AS '10',
    SUM(CASE WHEN t.variety_group = 20 THEN t.theSum ELSE 0 END) AS '20',
    SUM(CASE WHEN t.variety_group = 30 THEN t.theSum ELSE 0 END) AS '30'
FROM
(
    SELECT ryot_code, ryot_name, variety_group, SUM(total_area) AS theSum
    FROM field_survey_trn
    WHERE unit_code = '01' AND season_cntrl = 20
        AND crop_type_code NOT IN (11,12) AND ryot_code != 0
    GROUP BY ryot_code, ryot_name, variety_group
) t
GROUP BY t.ryot_code, t.ryot_name

edited Nov 3, 2015 at 9:39

answered Nov 3, 2015 at 8:06

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Gaurav Lad Over a year ago

I think there will be problem if variety_group values are increase from 10, 20, 30, 40, 50 .... and so on.

Tim Biegeleisen Over a year ago

Agreed. This answer assumes that he has a small and fixed number of variety groups. Let's wait for the owner of the question to also comment.

Ravi Over a year ago

had to remove semi colon after ** GROUP BY ryot_code, ryot_name, variety_group**, I guess it was not letting execute rest of sql (I am using mysql).

Tim Biegeleisen Over a year ago

Thanks for pointing this out. Because your query was so complex I didn't have the time to create a Fiddle for it.

Ravi Over a year ago

Yes I had limited number of variety groups. There is another option Pivot to solve it

Matt · Accepted Answer · 2015-11-03 10:48:43Z

1

you can do this in SQL using PIVOT.

SELECT * FROM

(
   SELECT ryot_code, ryot_name, variety_group, total_area
FROM field_survey_trn
WHERE unit_code = '01'
  AND season_cntrl = 20
  AND crop_type_code NOT IN (11,12)
  AND ryot_code != 0

) as my_name

PIVOT

(

    SUM(total_area)

    FOR [variety_group] IN ([10], [20], [30])
) piv1;

edited Nov 3, 2015 at 10:48

Matt

15.1k28 gold badges109 silver badges161 bronze badges

answered Nov 3, 2015 at 10:40

Parvej Bapary

396 bronze badges

Comments

Gaurav Lad · Accepted Answer · 2015-11-03 08:10:15Z

0

Instead of result as it would be preferred if you design it in single column of variety group with comma separated values like 10, 30 for ryot_code 00010004

If you like that suggestion then it can be done using below query

`SELECT group_concat(variety_group separator ',') INTO var_grp, 
    ryot_code, ryot_name, SUM(total_area)
 FROM field_survey_trn
 WHERE unit_code = '01'
 AND season_cntrl = 20
 AND crop_type_code NOT IN (11,12)
 AND ryot_code != 0;`

Hope this works as expected

answered Nov 3, 2015 at 8:10

Gaurav Lad

1,8081 gold badge17 silver badges31 bronze badges

Collectives™ on Stack Overflow

Sql: show data in a single row instead of different columns

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related