<div>Hi Majid,</div><div> </div><div>Use following query to get desired output.</div><div>select * from crosstab('select * from khan order by 1,2'<strong>,'select distinct district from khan order by 1'</strong>) as ct(survey_date date, Khuzdar int, Loralai int, Zhob int);</div><div> </div><div>By using this query, you have null values on some columns. To sum these columns, you can use coalesce function as in the below example.</div><div>select *,coalesce(Khuzdar,0)+coalesce(Loralai,0)+coalesce(Zhob,0) as total from crosstab('select * from khan order by 1,2','select distinct district from khan order by 1') as ct(survey_date date, Khuzdar int, Loralai int, Zhob int);</div><div> </div><div>You can have a look crosstab(text,text) in documentation.</div><div>https://www.postgresql.org/docs/9.6/static/tablefunc.html</div><div> </div><div>İyi çalışmalar.</div><div>Samed YILDIRIM</div><div> </div><div> </div><div> </div><div>16.06.2017, 15:36, "Majid Khan" <mk(dot)swati(at)gmail(dot)com>:</div><blockquote type="cite"><div> <div>---------- Forwarded message ----------<br />From: "Majid Khan" <<a href="mailto:mk(dot)swati(at)gmail(dot)com">mk(dot)swati(at)gmail(dot)com</a>><br />Date: Jun 12, 2017 20:48<br />Subject: Re: [SQL] crosstab category mix<br />To: "Steve Midgley" <<a href="mailto:science(at)misuse(dot)org">science(at)misuse(dot)org</a>><br />Cc:<br /> <blockquote style="margin:0 0 0 0.8ex;border-left:1px #ccc solid;padding-left:1ex;"><div>hi,<div> </div><div>thanks for response. Here attached is the data with actual summary and query result.Also I am applying the following query.</div><div> </div><div><div><strong><font color="#0000ff">SELECT * FROM crosstab('SELECT</font></strong></div><div><strong><font color="#0000ff">survey_date,</font></strong></div><div><strong><font color="#0000ff">district,</font></strong></div><div><strong><font color="#0000ff">total</font></strong></div><div><strong><font color="#0000ff">FROM</font></strong></div><div><strong><font color="#0000ff">sample') AS ct(survey_date varchar, khuzdar NUMERIC ,loralai NUMERIC,zhob NUMERIC);</font></strong></div><div> </div></div><div><font color="#000000">Regards,</font></div><div><font color="#000000">Majid</font></div></div><div><div> <div>On Mon, Jun 12, 2017 at 6:46 PM, Steve Midgley <span><<a target="_blank" href="mailto:science(at)misuse(dot)org">science(at)misuse(dot)org</a>></span> wrote:<blockquote style="margin:0 0 0 0.8ex;border-left:1px #ccc solid;padding-left:1ex;"><div><div><div><div> <div> <div>On Jun 12, 2017 12:53 AM, "Majid Khan" <<a target="_blank" href="mailto:mk(dot)swati(at)gmail(dot)com">mk(dot)swati(at)gmail(dot)com</a>> wrote:<blockquote style="margin:0 0 0 0.8ex;border-left:1px #ccc solid;padding-left:1ex;"><div><div><div style="margin:0in 0in 0pt;"><div style="font-size:18pt;"><span style="font-size:12pt;line-height:115%;"><font size="2" face="arial, helvetica, sans-serif" color="#000000">Hi,</font></span></div><div><table style="font-size:13px;margin:0px;padding:0px;border:0px;font-variant-numeric:inherit;font-stretch:inherit;line-height:inherit;font-family:Arial,"Helvetica Neue",Helvetica,sans-serif;vertical-align:baseline;border-collapse:collapse;"><tbody style="margin:0px;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;font-size:inherit;line-height:inherit;font-family:inherit;vertical-align:baseline;"><tr style="margin:0px;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;font-size:inherit;line-height:inherit;font-family:inherit;vertical-align:baseline;"><td style="padding:0px 15px 0px 0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;font-size:inherit;line-height:inherit;font-family:inherit;vertical-align:top;"><div style="margin:0px;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;font-size:inherit;line-height:inherit;font-family:inherit;vertical-align:baseline;text-align:center;min-width:46px;"><span style="line-height:115%;"><a title="Click to mark as favorite question (click again to undo)" target="_blank" style="margin:0px auto 2px;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;font-size:1px;line-height:inherit;font-family:inherit;vertical-align:baseline;background-image:url('');background-size:initial;background-repeat:no-repeat;overflow:hidden;display:block;width:40px;height:30px;background-position:0px -120px;" href="https://stackoverflow.com/questions/44487568/postgres-cross-tabs-mixes-categories-in-result-set#">favorite</a></span><div style="margin:0px;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;font-size:inherit;line-height:inherit;font-family:inherit;vertical-align:baseline;"> </div></div></td><td style="padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;font-size:inherit;line-height:inherit;font-family:inherit;vertical-align:top;"><div style="margin:0px;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;font-size:inherit;line-height:inherit;font-family:inherit;vertical-align:baseline;"><div style="margin:0px 0px 5px;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;font-size:15px;line-height:1.3;font-family:inherit;vertical-align:baseline;width:660px;"><p style="margin:0px 0px 1em;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;font-size:inherit;line-height:inherit;font-family:inherit;vertical-align:baseline;clear:both;"><span style="line-height:115%;">I have a query returning me three columns 'Date', 'District' and 'Total'. There are four districts but some time a district may not have record for corresponding date. I want to put all my districts in column and their total in rows as ..</span></p><p style="margin:0px 0px 1em;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;font-size:inherit;line-height:inherit;font-family:inherit;vertical-align:baseline;clear:both;"><span style="line-height:115%;">Date, District1, District2, District3, District4</span></p><p style="margin:0px 0px 1em;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;font-size:inherit;line-height:inherit;font-family:inherit;vertical-align:baseline;clear:both;"><span style="line-height:115%;">While in my rows will be date along with the total value per district.</span></p><p style="margin:0px 0px 1em;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;font-size:inherit;line-height:inherit;font-family:inherit;vertical-align:baseline;clear:both;"><span style="line-height:115%;">I am using crosstab to get the desired results. But in the result total for one district is listed in other district though the datewise grand total is same.</span></p><p style="margin:0px 0px 1em;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;font-size:inherit;line-height:inherit;font-family:inherit;vertical-align:baseline;clear:both;"><span style="line-height:115%;">I don't know why crosstab query mixes categories. Please help.</span></p></div></div></td></tr></tbody></table><span style="line-height:115%;"><font color="#000000"><font face="arial, helvetica, sans-serif">Regards,</font></font></span></div><div><span style="line-height:115%;"><font face="arial, helvetica, sans-serif" color="#000000">Majid</font></span></div></div></div></div></blockquote></div></div></div><div> </div></div></div><div><div><div><blockquote style="margin:0 0 0 0.8ex;border-left:1px #ccc solid;padding-left:1ex;"><div> </div></blockquote></div><span style="font-family:sans-serif;">Please provide sample data and sql to allow us to see where your problem is. </span></div><div> </div><div><span><font color="#888888"><span style="font-family:sans-serif;">Steve </span></font></span></div></div></div></blockquote></div> <div> </div></div></div></blockquote></div></div>,<p> </p><span>--<br />Sent via pgsql-sql mailing list (<a href="mailto:pgsql-sql(at)postgresql(dot)org">pgsql-sql(at)postgresql(dot)org</a>)<br />To make changes to your subscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-sql">http://www.postgresql.org/mailpref/pgsql-sql</a></span></blockquote>