Dynamic Columns

Hi all,

these are my tables :
CREATE TABLE [dbo].[mh1](
[mh1id] [int] IDENTITY(1,1) NOT NULL,
[mh1init] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
[mh1name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
[seq] [smallint] NOT NULL,
[date_created] [datetime] NULL,
[user_created] [int] NOT NULL,
[date_modified] [datetime] NULL,
[user_modified] [int] NOT NULL,
CONSTRAINT [PK_mh1] PRIMARY KEY CLUSTERED
(
[mh1id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[mh2](
[mh2id] [int] IDENTITY(1,1) NOT NULL,
[mh1id] [int] NOT NULL,
[mh2init] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
[mh2name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
[seq] [smallint] NOT NULL,
[date_created] [datetime] NULL,
[user_created] [int] NOT NULL,
[date_modified] [datetime] NULL,
[user_modified] [int] NOT NULL,
CONSTRAINT [PK_mh2] PRIMARY KEY CLUSTERED
(
[mh2id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

i need an output from
"SELECT mh1name FROM mh1"
"SELECT COUNT(mh2id) AS total FROM mh2 WHERE mh1id =
[@iterate_mh1id]"
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.

sorry if my question a bit weird coz i'm newbie in MSSQL.

Cheers.

Re: Dynamic Columns

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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx