Posted by aCe on 08/23/07 08:49

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.

[Back to original message]