July 12, 2015 at 9:10 pm
How do you query the data to return it in this format?
BookTitle1
BookGenre1
TitleName1
TitleName2
TitleName3
TitleName4
TitleName5
BookTitle2
BookGenre2
TitleName6
TitleName7
TitleName8
TitleName9
TitleName10
BookTitle3
BookGenre3
TitleName11
TitleName12
TitleName13
TitleName14
TitleName15
BookTitle4
BookGenre4
TitleName16
TitleName17
TitleName18
TitleName19
TitleName20
BookTitle5
BookGenre5
TitleName21
TitleName22
TitleName23
TitleName24
TitleName25
CREATE TABLE [dbo].[BookTest](
[BookID] [int] NOT NULL,
[BookTitle] [varchar](50) NULL,
[BookGenre] [varchar](50) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[TitleTest](
[TitleID] [int] NOT NULL,
[BookID] [int] NOT NULL,
[TitleName] [varchar](50) NULL
) ON [PRIMARY]
INSERT INTO [Test].[dbo].[BookTest] VALUES(1, 'BookTitle1', 'BookGenre1')
INSERT INTO [Test].[dbo].[BookTest] VALUES(2, 'BookTitle2', 'BookGenre2')
INSERT INTO [Test].[dbo].[BookTest] VALUES(3, 'BookTitle3', 'BookGenre3')
INSERT INTO [Test].[dbo].[BookTest] VALUES(4, 'BookTitle4', 'BookGenre4')
INSERT INTO [Test].[dbo].[BookTest] VALUES(5, 'BookTitle5', 'BookGenre5')
INSERT INTO [Test].[dbo].[TitleTest] VALUES(1, 1, 'TitleName1')
INSERT INTO [Test].[dbo].[TitleTest] VALUES(2, 1, 'TitleName2')
INSERT INTO [Test].[dbo].[TitleTest] VALUES(3, 1, 'TitleName3')
INSERT INTO [Test].[dbo].[TitleTest] VALUES(4, 1, 'TitleName4')
INSERT INTO [Test].[dbo].[TitleTest] VALUES(5, 1, 'TitleName5')
INSERT INTO [Test].[dbo].[TitleTest] VALUES(6, 2, 'TitleName6')
INSERT INTO [Test].[dbo].[TitleTest] VALUES(7, 2, 'TitleName7')
INSERT INTO [Test].[dbo].[TitleTest] VALUES(8, 2, 'TitleName8')
INSERT INTO [Test].[dbo].[TitleTest] VALUES(9, 2, 'TitleName9')
INSERT INTO [Test].[dbo].[TitleTest] VALUES(10, 2, 'TitleName10')
INSERT INTO [Test].[dbo].[TitleTest] VALUES(11, 3, 'TitleName11')
INSERT INTO [Test].[dbo].[TitleTest] VALUES(12, 3, 'TitleName12')
INSERT INTO [Test].[dbo].[TitleTest] VALUES(13, 3, 'TitleName13')
INSERT INTO [Test].[dbo].[TitleTest] VALUES(14, 3, 'TitleName14')
INSERT INTO [Test].[dbo].[TitleTest] VALUES(15, 3, 'TitleName15')
INSERT INTO [Test].[dbo].[TitleTest] VALUES(16, 4, 'TitleName16')
INSERT INTO [Test].[dbo].[TitleTest] VALUES(17, 4, 'TitleName17')
INSERT INTO [Test].[dbo].[TitleTest] VALUES(18, 4, 'TitleName18')
INSERT INTO [Test].[dbo].[TitleTest] VALUES(19, 4, 'TitleName19')
INSERT INTO [Test].[dbo].[TitleTest] VALUES(20, 4, 'TitleName20')
INSERT INTO [Test].[dbo].[TitleTest] VALUES(21, 5, 'TitleName21')
INSERT INTO [Test].[dbo].[TitleTest] VALUES(22, 5, 'TitleName22')
INSERT INTO [Test].[dbo].[TitleTest] VALUES(23, 5, 'TitleName23')
INSERT INTO [Test].[dbo].[TitleTest] VALUES(24, 5, 'TitleName24')
INSERT INTO [Test].[dbo].[TitleTest] VALUES(25, 5, 'TitleName25')
July 12, 2015 at 9:43 pm
Quick suggestion, use union all and order on the two id columns, here is an example
😎
USE Test;
GO
SET NOCOUNT ON;
;WITH BASE_DATA AS
(
SELECT
BT.BookID
,0 AS TitleID
,BT.BookTitle AS OUT_TEXT
FROM dbo.BookTest BT
UNION ALL
SELECT
BT.BookID
,0 AS TitleID
,BT.BookGenre AS OUT_TEXT
FROM dbo.BookTest BT
UNION ALL
SELECT
BT.BookID
,TT.TitleID AS TitleID
,TT.TitleName AS OUT_TEXT
FROM dbo.BookTest BT
INNER JOIN dbo.TitleTest TT
ON BT.BookID = TT.BookID
)
SELECT
BD.OUT_TEXT
FROM BASE_DATA BD
ORDER BY BD.BookID,BD.TitleID;
Results
BookTitle1
BookGenre1
TitleName1
TitleName2
TitleName3
TitleName4
TitleName5
BookGenre2
BookTitle2
TitleName6
TitleName7
TitleName8
TitleName9
TitleName10
BookTitle3
BookGenre3
TitleName11
TitleName12
TitleName13
TitleName14
TitleName15
BookGenre4
BookTitle4
TitleName16
TitleName17
TitleName18
TitleName19
TitleName20
BookTitle5
BookGenre5
TitleName21
TitleName22
TitleName23
TitleName24
TitleName25
July 13, 2015 at 10:02 pm
That worked! Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply