Possibility of Dynamic SQL Query

[mrmmickle1]

I have data that looks like this:

Excel 2012
	A	B	C	D	E	F	G
1	Payor	NC	SC	TN	KY	MA	NH
2	ABC Co.	0	0	0	1	1	1
3	BCD Co.	0	0	0	0	0	1
4	CDE Co.	1	1	1	0	0	0
5	DEF Co.	1	1	1	1	1	1
Sheet1

I would like to have a way to create a query that returns only fields with values above 0 for instance....

If I queried ABC Co. I would only want to see this as a result:

Excel 2012
	A	B	C	D
11	Payor	KY	MA	NH
12	ABC Co.	1	1	1
Sheet1

If I queried BCD Co. I would only want to see this as a result:

Excel 2012
	A	B
14	Payor	NH
15	BCD Co.	1
Sheet1

If I queried DEF Co. I would only want to see this as a result:

Excel 2012
	A	B	C	D	E	F	G
17	Payor	NC	SC	TN	KY	MA	NH
18	DEF Co.	1	1	1	1	1	1
Sheet1

Is this possible? My issue is the data I have has columns for all 50 states...So I would not want to display the ones with 0's...\
If there is a better format I would be open to it! I am just at a loss for this situation. Any help would be appreciated.

 

[Kyle123]

No, SQL doesn't work like that, it expects your data to be normalised, which yours isn't.

Is that really how the data is stored? It looks like the result of a Pivot/Cross tab query

 

[mrmmickle1]

It really is just like that.....

I believe a third party company just imported a spreadsheet into access. The data is vital to the project I'm working on. I can probably write some code to reorganize it.

What is a suggested data format? Like This?

Excel 2012
	A	B	C
26	Payor	ST	QTY
27	ABC Co.	KY	1
28	ABC Co.	MA	1
29	ABC Co.	NH	1
30	BCD Co.	NH	1
31	CDE Co.	NC	1
32	CDE Co.	SC	1
33	CDE Co.	TN	1
34	DEF Co.	NC	1
35	DEF Co.	SC	1
36	DEF Co.	TN	1
37	DEF Co.	KY	1
38	DEF Co.	MA	1
39	DEF Co.	NH	1
Sheet1

If I can normalize the data like that is it possible to create a query that would return:

Excel 2012
	A	B	C
26	Payor	ST	QTY
27	ABC Co.	KY	1
28	ABC Co.	MA	1
29	ABC Co.	NH	1
30	Total		3
Sheet1

Excel 2012
	A	B	C
32	Payor	ST	QTY
33	BCD Co.	NH	1
34	Total		1
Sheet1

 

[Kyle123]

Generally, yes that's better format, you'd probably have 3 tables though, a customer table, a state table and a customer-state join table.

Based on your example, you'd want:
<font face="Courier New" size="2">
<font color = "blue">SELECT</font> <font color = "maroon">payor</font><font color = "silver">,</font>
<br/>       <font color = "maroon">st</font><font color = "silver">,</font>
<br/>       <font color = "fuchsia"><i>Sum</i></font><font color = "maroon">(</font><font color = "maroon">qty</font><font color = "maroon">)</font>
<br/><font color = "blue">FROM</font>   <font color = "maroon">mytable</font>
<br/><font color = "blue">WHERE</font>  <font color = "maroon">payor</font> <font color = "silver">=</font> <font color = "red">'ABC Co.'</font>
<br/>       <font color = "blue">AND</font> <font color = "maroon">qty</font> <font color = "silver">></font> <font color = "black">0</font>
<br/><font color = "blue">GROUP</font>  <font color = "blue">BY</font> <font color = "maroon">payor</font><font color = "silver">,</font>
<br/>          <font color = "maroon">st</font> 
</font>

If the Qty is always one though, you could probably dispense with the Qty field altogether assuming that there will only be a record in the join table should there be a qty

 

[mrmmickle1]

Kyle123,

Thanks so much for the help. Looks like I have to figure out how to reorganize all of this data before I can use it ahahah

I was curious what it would look like in the optimal format you propose.

Excel 2012
	A	B	C
7	ID	State	Full Name
8	1	NC	North Carolina
9	2	GA	Georgia
Sheet1

Excel 2012
	A	B	C	D	E	F	G
1	ID	Co	Address 1	Address 2	St	City	Zip
2	1	ABC Co.	123 Main St	Ste 1	NC	Greensboro	23756
3	2	DEF Co.	123 1st St	Ste 2	GA	Atlanta	35698
Sheet1

How would I link the Quantities into this set up (There are not all 1's there are many different values)??

 

[Kyle123]

You'd have something like this:
<table width="640" border="0" align="center" cellpadding="2" cellspacing="0" style="border-collapse:collapse;background-color:#9d2600;border:5px solid #9d2600;color:black;font-size:100%;font-family:arial,helvetica,sans-serif;"><tbody><tr><td colspan="4" style="padding:2px;color:white;font-size:150%;border:2px solid #9d2600;text-align:center;">CustomerStateQty</td></tr><tr><td valign="top" width="25%" style="text-align:center;background-color:white;color:Black;font-size:100%;border:2px solid #9d2600;">ID/Key</td><td valign="top" width="25%" style="text-align:center;background-color:white;border:2px solid #9d2600;">CustomerId</td><td valign="top" width="25%" style="text-align:center;background-color:white;border:2px solid #9d2600;">StateId</td><td valign="top" width="25%" style="text-align:center;background-color:white;border:2px solid #9d2600;">Qty</td></tr><tr><td valign="top" style="background-color:white;color:Black;font-size:100%;border:2px solid #9d2600;">1</td><td valign="top" style="background-color:white;border:2px solid #9d2600;">1</td><td valign="top" style="background-color:white;border:2px solid #9d2600;">2</td><td valign="top" style="background-color:white;border:2px solid #9d2600;">10</td></tr><tr><td valign="top" style="background-color:white;color:Black;font-size:100%;border:2px solid #9d2600;">2</td><td valign="top" style="background-color:white;border:2px solid #9d2600;">1</td><td valign="top" style="background-color:white;border:2px solid #9d2600;">3</td><td valign="top" style="background-color:white;border:2px solid #9d2600;">5</td></tr><tr><td valign="top" style="background-color:white;color:Black;font-size:100%;border:2px solid #9d2600;">3</td><td valign="top" style="background-color:white;border:2px solid #9d2600;">1</td><td valign="top" style="background-color:white;border:2px solid #9d2600;">1</td><td valign="top" style="background-color:white;border:2px solid #9d2600;">7</td></tr></tbody></table>

Then:

<font face="Courier New" size="2">
<font color = "blue">SELECT</font> <font color = "maroon">customers</font><font color = "silver">.</font><font color = "maroon">co</font><font color = "silver">,</font>
<br/>       <font color = "maroon">states</font><font color = "silver">.</font><font color = "maroon">[full name]</font><font color = "silver">,</font>
<br/>       <font color = "fuchsia"><i>Sum</i></font><font color = "maroon">(</font><font color = "maroon">customerstateqty</font><font color = "silver">.</font><font color = "maroon">qty</font><font color = "maroon">)</font>
<br/><font color = "blue">FROM</font>   <font color = "maroon">customerstateqty</font>
<br/>       <font color = "blue">INNER</font> <font color = "blue">JOIN</font> <font color = "maroon">customers</font>
<br/>               <font color = "blue">ON</font> <font color = "maroon">customerid</font> <font color = "silver">=</font> <font color = "maroon">customers</font><font color = "silver">.</font><font color = "maroon">id</font>
<br/>       <font color = "blue">INNER</font> <font color = "blue">JOIN</font> <font color = "maroon">states</font>
<br/>               <font color = "blue">ON</font> <font color = "maroon">stateid</font> <font color = "silver">=</font> <font color = "maroon">states</font><font color = "silver">.</font><font color = "maroon">id</font>
<br/><font color = "blue">WHERE</font>  <font color = "maroon">customer</font><font color = "silver">.</font><font color = "maroon">co</font> <font color = "silver">=</font> <font color = "red">'ABC co.'</font>
<br/><font color = "blue">GROUP</font>  <font color = "blue">BY</font> <font color = "maroon">co</font><font color = "silver">,</font>
<br/>          <font color = "maroon">[full name]</font> 
</font>


Does that make sense?

 

[mrmmickle1]

I think I got it. Thanks! I appreciate the help. I'll let you know if I have further inquiries.

 

[Kyle123]

Glad I could help

 

[james_lankford]

this is a query that will make it look like the first table in your post #3

select 
  Payor, 
  'NC' as State, 
  NC  as Qty
from 
  table1
where 
  NC > 0 
  and 
  Payor = [param_Payor]


union 


select 
  Payor, 
  'SC' as State, 
  SC  as Qty
from 
  table1
where 
  SC > 0 
  and 
  Payor = [param_Payor]


union 


select 
  Payor, 
  'TN' as State, 
  TN  as Qty
from 
  table1
where 
  TN > 0 
  and 
  Payor = [param_Payor]


union 


select 
  Payor, 
  'KY' as State, 
  KY  as Qty
from 
  table1
where 
  KY > 0 
  and 
  Payor = [param_Payor]


union 


select 
  Payor, 
  'MA' as State, 
  MA  as Qty
from 
  table1
where 
  MA > 0 
  and 
  Payor = [param_Payor]


UNION select 
  Payor, 
  'NH' as State, 
  NH  as Qty
from 
  table1
where 
  NH > 0 
  and 
  Payor = [param_Payor];

 

[mrmmickle1]

Thanks for all of the input guys. I'm reorganizing the data today. I'll let you know if I run into any issues.