|
|
Posted by Erland Sommarskog on 08/23/07 21:54
aCe (acerahmat@gmail.com) writes:
> like :
>|-----------------|-----------------|-----------------|---|-------------------|
>|columns_mh1name_1|columns_mh1name_2|columns_mh1name_3|...|
> columns_mh1name_(n)|
>|-----------------|-----------------|-----------------|---|-------------------|
>|total_mh1name_1--|total_mh1name_2--|total_mh1name_3--|---|
> total_mh1name_(n)--|
>|-----------------|-----------------|-----------------|---|-------------------|
>
> so the output will create new column after inserting new row mh1 and
> mh2 just follow the top
> without alter the query(SELECT).
> is it possible to create it with stored procedure or function or else.
The idiom for static crosstab query is this:
SELECT Year,
Emp1 = MIN(CASE EmployeeID WHEN 1 THEN cnt END),
Emp2 = MIN(CASE EmployeeID WHEN 2 THEN cnt END),
Emp3 = MIN(CASE EmployeeID WHEN 3 THEN cnt END),
Emp4 = MIN(CASE EmployeeID WHEN 4 THEN cnt END),
Emp5 = MIN(CASE EmployeeID WHEN 5 THEN cnt END),
Emp6 = MIN(CASE EmployeeID WHEN 6 THEN cnt END),
Emp7 = MIN(CASE EmployeeID WHEN 7 THEN cnt END),
Emp8 = MIN(CASE EmployeeID WHEN 8 THEN cnt END),
Emp9 = MIN(CASE EmployeeID WHEN 9 THEN cnt END)
FROM (SELECT Year = Year(OrderDate), EmployeeID, cnt = COUNT(*)
FROM Orders
GROUP BY Year(OrderDate), EmployeeID) AS d
GROUP BY Year
I was not able to understand your table, but this example runs in
Northwind. The inner query is there only to provide data to the
outer query which is the crosstab query.
To make a dynamic crosstab, you need to generate SQL code like the above.
There are no way to write a query which returns a dynamic number of
columns. This is because a SELECT statement returns a table, and a table
has a fixed set of columns.
Writing this dynamic SQL is not that funny. You may be interested in
looking at the third-party tool RAC, at http://www.rac4sql.net.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|