Try this...

IF OBJECT_ID('tempdb..#Example','U') IS NOT NULL

DROP TABLE #Example;

CREATE TABLE #Example (

id INT,

AZ INT NULL,

NY INT NULL,

PA INT NULL

);

GO

INSERT#Example (id,AZ,NY,PA) VALUES (1, '5','7','5'),(2, '2','3','5'),(3, '3','2','5');

SELECT

e.id,

x.State,

x.Value

FROM

#Example e

CROSS APPLY (VALUES ('AZ',...

No worries... Take the last query I supplied and replace SUM with MAX and see if that gets you the results you're looking for. If not, see if you can...

Actually, I think the FORMAT function has been shown to be unnecessarily expensive and should be avoided altogether.

FORMAT() is nice and all, but…

There have also been a few threads here...

itortu (9/8/2016)

---

Do you think that using dynamic sql the only way this can be achieved? I like the first code example that you gave me. If that can be used...

itortu (9/8/2016)

---

Hello Jason,

I am not sure if I completely follow what your comments regarding the parameters are, but let me give it a try.

The situation is that this...

If it's the "just the specific combination that's requested" version, something like the following should work...

SET NOCOUNT ON;

DECLARE

@TablesToUse VARCHAR(50) = 'Instruments, Scoring, Style',

@DeBug BIT = 1;

IF OBJECT_ID('tempdb..#TablesToUse', 'U') IS...

Just a heads up... I do see a screw up in the code I posted yesterday... All the UNIONs should actually be UNION ALLs...

itortu (9/8/2016)

---

Hello Jason, I have a question, I checked the results that your code returns and I think it does A LOT of what I need to have as a...

Jeff will be along shortly to find the flaw in my "quirky" (he always manages to make me slap my forehead when I do one of these)...

In any case make...

The "Quirky" method does come to mind as a viable option but I have a feeling that's what you're referring to when you say "some funky thing with temp tables"...

Just...

Phil Parkin (9/7/2016)

And that's where a shared set of SQL Prompt formatting options really works.

One thing missing from SQL Prompt, however, is the ability to change between 'my' settings (whatever...

Luis Cazares (9/7/2016)

---

Jason A. Long (9/7/2016)

---

Speaking for all the jerk code reviewers of the world... Yes, formatting counts, yes I'll give you crap over it (when you have to...

Speaking for all the jerk code reviewers of the world... Yes, formatting counts, yes I'll give you crap over it (when you have to read through the code from...

This is presented completely different than the ask on the MSDN forum... https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0b897a76-3d65-476d-8f40-4f2242629da0/write-query-with-all-possible-permutations?forum=transactsql

Any way... Try this...

USE tempdb;

GO

/*

CREATE TABLE dbo.TopSellerInstrumentsLastWeek

(

RegionCHAR(2)

,SalesRankINT

,pf_idVARCHAR(15)

,PurchasesDECIMAL(18,4)

,PageIDINT

,instrument_idINT

);

CREATE TABLE dbo.TopSellerScoringLastWeek

(

RegionCHAR(2)

,SalesRankINT

,pf_idVARCHAR(15)

,PurchasesDECIMAL(18,4)

,PageIDINT

,scoring_idINT

);

CREATE TABLE dbo.TopSellerStyleLastWeek

(

RegionCHAR(2)

,SalesRankINT

,pf_idVARCHAR(15)

,PurchasesDECIMAL(18,4)

,PageIDINT

,style_idINT

);

INSERT INTO TopSellerStyleLastWeek VALUES (1 , 1, 'MN0163104',...

Same results I got... At least it's consistent. 😉