Merging records

Question

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

Answer 1

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