November 1, 2016 at 8:02 am
I have a table that has a column header of delegatesname and then market. in the market is CA, CT, NY, as examples. I did a query to find out the count of markets by delegatesname to see how many pivots I needed to do and below are my queries:
/*now work with the multiple markets to find out how many markets are per delegatesname do a count*/
select distinct delegatesname, market into anthemq.dbo.deletest from anthemq.dbo.deleoriginal group by DelegatesName, Market;
select distinct delegatesname, COUNT(market) as a from anthemq.dbo.deletest group by DelegatesName order by a desc;/*most is 49 */
select delegatesname, m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11,m12,m13,m14,m15,m16,m17,m18,m19,m20,m21,m22,m23,m24,m25,m26,m27,m28,m29,m30,m31,m32,m33,m34,m35,m36,m37,m38,m39,m40,m41,
m42,m43,m44,m45,m46,m47,m48,m49
into anthemq.dbo.delemarket/*holds the contactname retain until final query*/
from (select delegatesname, market, 'market' + CAST(row_number() over(partition by delegatesname order by delegatesname) as char(10))columnsequence
from anthemq.dbo.deletest
) temp pivot(max(market)
for columnsequence in (m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11,m12,m13,m14,m15,m16,m17,m18,m19,m20,m21,m22,m23,m24,m25,m26,m27,m28,m29,m30,m31,m32,m33,m34,m35,m36,m37,m38,m39,m40,m41,
m42,m43,m44,m45,m46,m47,m48,m49))piv;
I can clearly see when doing a select on the deletest that I see what I should with the delegatesname filled in and the market filled in. But no matter what I do with pivoting even if I just try pivoting like the first 5 I get all null returns. The original field is a char(10). This is what I see in my table just a few of the top rows:
Delegatesname Market
Accordant Health ServicesCA
Accordant Health ServicesCT
Accordant Health ServicesGA
Accordant Health ServicesIN
November 1, 2016 at 8:44 am
You're creating columnsequences named 'market1', 'market2', etc., but you're looking for columnsequences 'm1', 'm2', etc. I just changed your script to create columnsequences with the shorter name.
select delegatesname, m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11,m12,m13,m14,m15,m16,m17,m18,m19,m20,m21,m22,m23,m24,m25,m26,m27,m28,m29,m30,m31,m32,m33,m34,m35,m36,m37,m38,m39,m40,m41,
m42,m43,m44,m45,m46,m47,m48,m49
into anthemq.dbo.delemarket/*holds the contactname retain until final query*/
from (select delegatesname, market, 'm' + CAST(row_number() over(partition by delegatesname order by delegatesname) as char(10))columnsequence
from anthemq.dbo.deletest
) temp pivot(max(market)
for columnsequence in (m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11,m12,m13,m14,m15,m16,m17,m18,m19,m20,m21,m22,m23,m24,m25,m26,m27,m28,m29,m30,m31,m32,m33,m34,m35,m36,m37,m38,m39,m40,m41,
m42,m43,m44,m45,m46,m47,m48,m49))piv;
Also, your row number is non-deterministic. I would order by market rather than delegatesname.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 1, 2016 at 9:37 am
Oh wow, it is usually the simplest things that cause an issue. Thanks so much
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply