How To Convert Access Crosstab Query to a SQL Server (T-SQL) Query

How do I convert the following Access crosstab query to T-SQL?
TRANSFORM Sum(qryAgingProjects03.SumOfTotal) AS SumOfSumOfTotal
SELECT qryAgingProjects03.BU5Digit, qryAgingProjects03.PSPROJ, qryAgingProjects03.COMPNY
FROM qryAgingProjects03
GROUP BY qryAgingProjects03.BU5Digit, qryAgingProjects03.PSPROJ, qryAgingProjects03.COMPNY
ORDER BY qryAgingProjects03.PERIOD DESC
PIVOT qryAgingProjects03.PERIOD;
I have researched through Google but the answers are extremely complex and do not seem to share any syntax.
LVL 16
Chuck WoodAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
You have to use a cte in SQL Server unless you have no other columns other than those listed below. If you don't, then use of a cte becomes optional. One could use a derived table expression instead of cte.
;with cte as
(
SELECT qryAgingProjects03.BU5Digit  -- grouping column1, row header1 in access
     , qryAgingProjects03.PSPROJ    -- grouping column2, row header2 in access
     , qryAgingProjects03.COMPNY    -- grouping column3, row header3 in access
     , qryAgingProjects03.SumOfTotal  -- aggregation column, value in access
     , qryAgingProjects03.PERIOD    -- spreading column,  column header in access
FROM qryAgingProjects03
finding.
)
Select qryAgingProjects03.BU5Digit  -- row header1
     , qryAgingProjects03.PSPROJ    -- row header2
     , qryAgingProjects03.COMPNY    -- row header3 
     , [12], [11], [10], [09], [08], [07], [06], [05], [04], [03], [02], [01]  -- column headers
From cte
Pivot (Sum(qryAgingProjects03.SumOfTotal) For PERIOD IN([12], [11], [10], [09], [08], [07], [06], [05], [04], [03], [02], [01]) As D;

Open in new window

With SQL Server, unlike Access, you have to know what will be the list of column headers, know as spreading column in SQL Server ([12], [11], [10], [09], [08], [07], [06], [05], [04], [03], [02], [01]).

You also may replace the list of these spreading column using dynamic SQL. Here I have assumed your periods are months like 01 for Jan, 02 for Feb, etc. If you were missing [08] for example, that column will simply skip. It will not display column [08].

Mike


Note: line 14,  From cte is added.
I hope some other experts will comment on the validity of my statement about "use of a cte becomes optional" discuseed above.
PortletPaulEE Topic AdvisorCommented:
Use of a CTE (common table expression) is almost always optional.

The one thing that a CTE does that is difficult to achieve any other way is "recursion", but that is not required for this question.

So, yes, definitely, the CTE approach is optional here.e.g.
Select qryAgingProjects03.BU5Digit  -- row header1
     , qryAgingProjects03.PSPROJ    -- row header2
     , qryAgingProjects03.COMPNY    -- row header3 
     , [12], [11], [10], [09], [08], [07], [06], [05], [04], [03], [02], [01]  -- column headers
From (
         -- "derived table" instead
         SELECT qryAgingProjects03.BU5Digit  -- grouping column1, row header1 in access
              , qryAgingProjects03.PSPROJ    -- grouping column2, row header2 in access
              , qryAgingProjects03.COMPNY    -- grouping column3, row header3 in access
              , qryAgingProjects03.SumOfTotal  -- aggregation column, value in access
              , qryAgingProjects03.PERIOD    -- spreading column,  column header in access
         FROM qryAgingProjects03
          ) AS DT
Pivot (Sum(qryAgingProjects03.SumOfTotal) For PERIOD IN([12], [11], [10], [09], [08], [07], [06], [05], [04], [03], [02], [01]) As D;

Open in new window


bias: Personally I believe CTE's are vastly overused.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike EghtebasDatabase and Application DeveloperCommented:
Hi Paul,

re:> ... definitely, the CTE approach is optional ...

For pivot I agree we have the option of using using CTE or derived table. My question was on something else.

Is it possible to pivot directly from a table without CTE or derived table (under certain conditions)?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

John TsioumprisSoftware & Systems EngineerCommented:
Well the solution is not straightforward...
The key is the "PIVOT" keyword which essentially does the job but it has a major issue...the "IN" that follows doesn't accept a query as a source for the columns...
To overcome this drawback you have to use dynamic SQL in order to build a string that will be fed to the PIVOT....
Mike EghtebasDatabase and Application DeveloperCommented:
@tsgiannis,

I agree, this is why I have made mention of dynamic SQL in my solution above. It will totally depend whether   cwood-wm-com has the spreading columns as I have assumed [01], [02], etc. or needs to use dynamic sql.

Mike
Mike EghtebasDatabase and Application DeveloperCommented:
@ cwood-wm-com,

Summation and clarifications on my first post:
Your data source seems to be a query (possibly access query). This mean you have control over two items discussed below. If so then you do not have to use cte or derived table. You can just pivot qryAgingProjects03 (keep in mind, to do this, the following two conditions has to be met):

1. The columns in qryAgingProjects03 should be limited to the followings:
BU5Digit     -- row header1
PSPROJ        -- row header2
COMPNY     -- row header3
SumOfTotal  -- aggregation column
Period          -- spreading column

2. Your period column data could be fashined like below (otherwise you need to consider dynamic sql solution discussed before).
01
02
etc.

With items 1 and 2 above satisfied, your solution will be:
SELECT BU5Digit, PSPROJ, COMPNY    -- row headers
           , [12], [11], [10], [09], [08], [07], [06], [05], [04], [03], [02], [01]
FROM qryAgingProjects03 
PIVOT(SUM(SumOfTotal) FOR Period IN ( [12], [11], [10], [09], [08], [07], [06], [05], [04], [03], [02], [01]) ) AS P;

Open in new window

If condition 1 is not possible and you need to maintain some other columns in it then let us know to help out with either cte or derived table solution.


Mike
Chuck WoodAuthor Commented:
The periods are dynamic and include all of the periods in the table e.g. 201501, 201502, 201503, ...
How would I write a dynamic sql query to handle this?

Chuck
Mike EghtebasDatabase and Application DeveloperCommented:
To test the process so far, dynamic sql aside, for now lets consider hard coding periods as shown below:

SELECT BU5Digit, PSPROJ, COMPNY    -- row headers
           , [201501], [201502], [201503]
FROM qryAgingProjects03 
PIVOT(SUM(SumOfTotal) FOR Period IN ([201501], [201502], [201503]) ) AS P;

Open in new window


If this produces good result then we can say condition 1 is met and we can address the dynamic sql part.

Please comment on the 1st condition repeated below:
1. The columns in qryAgingProjects03 should be limited to the followings:
BU5Digit     -- row header1
PSPROJ        -- row header2
COMPNY     -- row header3
SumOfTotal  -- aggregation column
Period          -- spreading column

Question: Can you limit columns in this query to 5 columns? I think you can have other columns to apply where condition.
 
Mike
Mike EghtebasDatabase and Application DeveloperCommented:
Dynamic version (provided condition 1 is met):
DECLARE @Param NVARCHAR(1000); 
DECLARE @FinalSQL NVARCHAR(max);
SET     @Param = '[201501], [201502], [201503]';

@FinalSQL = 'SELECT BU5Digit, PSPROJ, COMPNY, ' + @Param + ' FROM qryAgingProjects03 
PIVOT(SUM(SumOfTotal) FOR Period IN (' + @Param + ')) AS P';
EXEC    SP_EXECUTESQL

Open in new window


Here again we have hard coded the periods in @Param but using dynamic sql. So, you need to post a new question asking how to populate @Param via a function call detecting all applicable periods per your specifications.

Mike
Chuck WoodAuthor Commented:
Condition 1 is met.  When I run the dynamic version, I receive tis error:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '@FinalSQL'.

Chuck
Chuck WoodAuthor Commented:
I added SET before @FinalSQL = 'SELECT BU5Digit, ... and received this error:
Msg 201, Level 16, State 10, Procedure sp_executesql, Line 1
Procedure or function 'sp_executesql' expects parameter '@statement', which was not supplied.

Chuck
Mike EghtebasDatabase and Application DeveloperCommented:
I have a temp data testing the solution I posted. This works fine. I am including it here for visaul comparison:
DECLARE @FinalSQL	NVARCHAR(1000);
DECLARE @Param NVARCHAR(100) 
SET     @Param = '[1], [2], [3]';
SET	@FinalSQL = 'SELECT custid,' +  @Param + ' FROM #t3Columns
PIVOT(SUM(freight) FOR shipperid IN (' + @Param +') ) AS P;'; 
EXEC    SP_EXECUTESQL @FinalSQL

Open in new window

Mike EghtebasDatabase and Application DeveloperCommented:
I got it. Aside from adding Set which was missing, we need to add the following:

EXEC    SP_EXECUTESQL @FinalSQL
Chuck WoodAuthor Commented:
That worked. Unfortunately, I really need to pull whatever Periods are in the table. Is it possible to select a GROUP BY list of Periods and build the @Param from them?

Chuck
Mike EghtebasDatabase and Application DeveloperCommented:
Please post a new question. Give sample data. This will be an isolated task and it needs to be looked at to provide a good solution for you.

This will be my suggestion. What do you think?

In the new question, give your solution that already works. As for @Param update per specification you describe. Providing some sample data will be very useful.

Mike
Chuck WoodAuthor Commented:
The solution worked to produce the result it was designed to produce. It did not, however, produce the result of the original Access query. While the dynamic query produced the periods explicitly stated in @Param, it does not dynamically pull all the periods in the table. I would have to modify the query every month, instead of just running it. I have attached some sample data to show the output of the Access query. If you would prefer to leave your solution as is, I can close this question and give partial credit.

Results of the original Access query
Chuck
Mike EghtebasDatabase and Application DeveloperCommented:
You don't have to give any points to me. Accept the solution from Paul. Or, request to close the question and refund all of the points. But don't delete the question.

Post another question with the solution thus far and sample data (Do not post the image of sample data, post electronic version so others can make a temp table to test it). Also above is the final data. Provide electronic version from qryAgingProjects03 you have, just small portion.

Subject: Dynamic sql help

Question Body: I have @param. need a function call to populate periods....

You could also wait for other experts to give you the solution you want.  

Thanks,

Mike
Mike EghtebasDatabase and Application DeveloperCommented:
Thanks Paul and Chuck,

It is just fine the way it is as far as I am concerned.

Mike
PortletPaulEE Topic AdvisorCommented:
Chuck & eghtebas, thanks.
Mike EghtebasDatabase and Application DeveloperCommented:
For the benefit of future readers of this thread, here is the summary of my findings:
You can make a pivot query directly from a table without using cte or derived table expression if all columns in the table are used in the pivot query. To demo this, below I have two temp tables: #t4Columns and #t3Columns. This demo shows that #t3Columns allows pivoting directly without using cte or derived table expression because it doesn't have the extra orderid column included in #t4Columns. For the reasons stated thus far, #t4Columns fails to produce correct pivot result (directly from the table) because of this extra column.

Here is the demo:
create table #t4Columns(Orderid int, custid int, shipperid int, freight decimal(8,2));
go
Insert Into #t4Columns(Orderid, custid, shipperid, freight) values
(10643,	1,	1,	29.46)
,(10692,	1,	2,	61.02)
,(10702,	1,	1,	23.94)
,(10835,	1,	3,	69.53)
,(10952,	1,	1,	40.42)
,(11011,	1,	1,	1.21)
,(10625,	2,	1,	43.90)
,(10926,	2,	3,	39.92)
,(10759,	2,	3,	11.99)
,(10308,	2,	3,	1.61)
,(10365,	3,	2,	22.00)
,(10507,	3,	1,	47.45)
,(10535,	3,	1,	15.64)
,(10573,	3,	3,	84.84)
,(10677,	3,	3,	4.03)
,(10682,	3,	2,	36.13)
,(10856,	3,	2,	58.43);
go
create table #t3Columns(custid int, shipperid int, freight decimal(8,2)); 
go
Insert Into #t3Columns(custid, shipperid, freight) 
Select custid, shipperid, freight From #t4Columns;
go
select * from #t4Columns;
go
select * from #t3Columns;
go
-- this works fine because of #t3Columns doesn't include any unused column.
SELECT custid, [1], [2], [3]
FROM #t3Columns
PIVOT(SUM(freight) FOR shipperid IN ([1],[2],[3]) ) AS P;
go
-- this fails because of #t4Columns does include an extra unused column (orderid).
SELECT custid, [1], [2], [3]
FROM #t4Columns
PIVOT(SUM(freight) FOR shipperid IN ([1],[2],[3]) ) AS P
go
-- but with a dervided table, orderid excluded, #t4Columns works fine this time.
SELECT custid, [1], [2], [3]
From (
   SELECT custid, shipperid, freight --, orderid -- excluded
   FROM #t4Columns)AS D 
PIVOT(SUM(freight) FOR shipperid IN ([1],[2],[3]) ) AS P;

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.