Merging records

Question

Hello friends.

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