Merging records

I am working on a project in ASP.Net, and I am using SQL Server 2005. My problem is something like that:

I have two Tables.

Table A : Which has fields ID and Category (ID is primary Key) Table B : Which has fields ID, CategoryID, Item

There is a relation A:ID ----> B.CaregoryID

Lets Add some records.

Table A:

ID      Category

1        Books

2        Fruits

3        Vegetables

Table B:

ID    CategoryID      Item

1    1                        Rytham of Music

2    1                        My B开发者_StackOverflowiography

3    1                        Jungal Book

4    2                        Apple

5    2                        Orenge

6    2                        Pinnaple

7    3                        Spinach

Like that there are two table and their records.

I want query something like that my final output must be in temparary table as like following

ID                        MergeItems

1                          Books -- From Table A

1                        Rytham of Music

2                        My Biography

3                        Jungal Book

2                          Fruits -- From Table A

4                        Apple

5                        Orenge

6                        Pinnaple

3                         Vegetables-- From Table A

7                        Spinach

I want to show this output in dropdown control in ASP.Net

I want a query in sql Server 2005. If you know please forword me

Thank you.

---

I think you were looking for a UNION ALL

DECLARE @TableA TABLE(
        ID INT,
        Category VARCHAR(50)
)

INSERT INTO @TableA (ID,Category) SELECT 1, 'Books'
INSERT INTO @TableA (ID,Category) SELECT 2, 'Fruits'
INSERT INTO @TableA (ID,Category) SELECT 3, 'Vegetables'

DECLARE @TableB TABLE(
        ID INT,
        CategoryID INT,
        Item VARCHAR(50)
)


INSERT INTO @TableB (ID,CategoryID,Item) SELECT 1,1,'Rytham of Music'
INSERT INTO @TableB (ID,CategoryID,Item) SELECT 2,1,'My Biography'
INSERT INTO @TableB (ID,CategoryID,Item) SELECT 3,1,'Jungal Book'
INSERT INTO @TableB (ID,CategoryID,Item) SELECT 4,2,'Apple'
INSERT INTO @TableB (ID,CategoryID,Item) SELECT 5,2,'Orenge'
INSERT INTO @TableB (ID,CategoryID,Item) SELECT 6,2,'Pinnaple'
INSERT INTO @TableB (ID,CategoryID,Item) SELECT 7,3,'Spinach'

SELECT  ID,
        MergedCategory
FROM    (
            SELECT  ID,
                    Category + ' -- From TableA' MergedCategory,
                    CAST(ID AS VARCHAR(10)) + '\' AS CategoryID
            FROM    @TableA
            UNION ALL
            SELECT  ID,
                    Item,
                    CAST(CategoryID AS VARCHAR(10)) + '\' + CAST(ID AS VARCHAR(10)) + '\'
            FROM    @TableB
        ) sub
ORDER BY CategoryID