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

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;

Select allOpen 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.

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)?

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....

@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

@ 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;

Select allOpen 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

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

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;

Select allOpen 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

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

Select allOpen 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

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

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

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

Select allOpen in new window

I got it. Aside from adding Set which was missing, we need to add the following:

EXEC    SP_EXECUTESQL @FinalSQL

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

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

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.


Chuck

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

Thanks Paul and Chuck,

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

Mike

Chuck & eghtebas, thanks.

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;

Select allOpen in new window