SQL Results in One Row

Hi guys, i have the following table setup

MEETINGS TABLE
ID      bigint      Unchecked
DOID      int      Checked
ActualDate      date      Checked
RaceDate      date      Checked
EventType      nchar(1)      Checked
Venue      nchar(50)      Checked
Events      int      Checked

CODES TABLE

ID      bigint      Unchecked
MeetingID      bigint      Checked
TAB      nchar(3)      Checked
Code      nchar(3)      Checked

MeetingID in the CODES table is a foreign key referencing ID in the MEETINGS table.

Now im not sure if i want is possible or not but here i go anyway:

I run the following query:

SELECT     Meetings.*, Codes.*
FROM         Meetings INNER JOIN
                      Codes ON Meetings.ID = Codes.MeetingID

And get the Following Results (CAPTURE IMAGE ATTACHED).

Ok, thats fine but what i would like to do is to get this down to a one row result.

so the second line TAB/CODE fields are appended to the end of the first row and so the one row would look something like this on the end of it TAB/Code and the second rows results merged in TAB/Code again.

Can this one row result be achieved? if so, how?

Thanks.
Capture.PNG
chudmarekAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ThomasianCommented:
Since you will have variable number of columns, you will need to use dynamic sql.

I did not include all the fields in the test script, but it should still work.
CREATE TABLE myMEETINGS (ID bigint, DOID int)
CREATE TABLE myCODES (ID bigint IDENTITY(1,1), MeetingID bigint, TAB nchar(3), Code nchar(3))

INSERT INTO myMEETINGS (ID, DOID) VALUES (1, 1047725)
INSERT INTO myMEETINGS (ID, DOID) VALUES (2, 1254434)
INSERT INTO myMEETINGS (ID, DOID) VALUES (3, 2434415)

INSERT INTO myCODES (MeetingID, TAB, Code) VALUES (1,'A','B')
INSERT INTO myCODES (MeetingID, TAB, Code) VALUES (1,'C','D')
INSERT INTO myCODES (MeetingID, TAB, Code) VALUES (1,'E','F')
INSERT INTO myCODES (MeetingID, TAB, Code) VALUES (2,'G','H')
INSERT INTO myCODES (MeetingID, TAB, Code) VALUES (3,'I','J')
INSERT INTO myCODES (MeetingID, TAB, Code) VALUES (3,'K','L')

GO

DECLARE @sql varchar(max)

SELECT @sql = ISNULL(@sql+',','') 
            + ' MAX(CASE WHEN C.rn=' + CAST(number as varchar) + ' THEN TAB END) TAB' + CAST(number as varchar)
            + ', MAX(CASE WHEN C.rn=' + CAST(number as varchar) + ' THEN CODE END) CODE' + CAST(number as varchar)
FROM master..spt_values
WHERE type='P'
      AND number BETWEEN 1 AND (SELECT TOP 1 COUNT(1) FROM myCODES GROUP BY MeetingID ORDER BY COUNT(1) DESC)
     
SET @sql =
'SELECT *
FROM
  myMEETINGS T1 OUTER APPLY
 (SELECT ' + @sql +
' FROM myMEETINGS M INNER JOIN
      (SELECT MeetingID, TAB, CODE, ROW_NUMBER() OVER (PARTITION BY MeetingID ORDER BY ID) rn
       FROM myCODES
      ) C ON C.MeetingID=M.ID 
  WHERE ID=T1.ID
 ) T2'

exec (@sql)
/*Result:
ID	DOID		TAB1	CODE1	TAB2	CODE2	TAB3	CODE3
1	1047725		A  	B 	C  	D 	E	F  
2	1254434		G	H	NULL	NULL	NULL	NULL
3	2434415		I  	J 	K 	L	NULL	NULL
*/

GO

DROP TABLE myCODES
DROP TABLE myMEETINGS

Open in new window

0
chudmarekAuthor Commented:
Hi Thomasian,

That's fantastic!!! I have one more question tho, how can i alter this query to select the results by
myMeetings.RaceDate (in format YYYY-MM-DD),.

so, the query you have shows me all the data as required but i want to add to the end of the query

AND myMeetings.RaceDate='2010-03-23'

Thanks again.
0
ThomasianCommented:
>>'SELECT T1.*, CONVERT(varchar(10),T1.RaceDate,120) [Race Date Text], T2.*
You can change this line to select the fields you want returned.


>>WHERE T1.RaceDate=''2010-03-23'''
This condition is added at the end of the sql string. If you need to filter the result from myMEETINGS table, you can add more conditions  here.
CREATE TABLE myMEETINGS (ID bigint, DOID int, RaceDate date)
CREATE TABLE myCODES (ID bigint IDENTITY(1,1), MeetingID bigint, TAB nchar(3), Code nchar(3))

INSERT INTO myMEETINGS (ID, DOID, RaceDate) VALUES (1, 1047725, '2010-03-23')
INSERT INTO myMEETINGS (ID, DOID, RaceDate) VALUES (2, 1254434, '2010-03-23')
INSERT INTO myMEETINGS (ID, DOID, RaceDate) VALUES (3, 2434415, '2010-03-24')

INSERT INTO myCODES (MeetingID, TAB, Code) VALUES (1,'A','B')
INSERT INTO myCODES (MeetingID, TAB, Code) VALUES (1,'C','D')
INSERT INTO myCODES (MeetingID, TAB, Code) VALUES (1,'E','F')
INSERT INTO myCODES (MeetingID, TAB, Code) VALUES (2,'G','H')
INSERT INTO myCODES (MeetingID, TAB, Code) VALUES (3,'I','J')
INSERT INTO myCODES (MeetingID, TAB, Code) VALUES (3,'K','L')

GO

DECLARE @sql varchar(max)

SELECT @sql = ISNULL(@sql+',','') 
            + ' MAX(CASE WHEN C.rn=' + CAST(number as varchar) + ' THEN TAB END) TAB' + CAST(number as varchar)
            + ', MAX(CASE WHEN C.rn=' + CAST(number as varchar) + ' THEN CODE END) CODE' + CAST(number as varchar)
FROM master..spt_values
WHERE type='P'
      AND number BETWEEN 1 AND (SELECT TOP 1 COUNT(1) FROM myCODES GROUP BY MeetingID ORDER BY COUNT(1) DESC)
     
SET @sql =
'SELECT T1.*, CONVERT(varchar(10),T1.RaceDate,120) [Race Date Text], T2.*
FROM
  myMEETINGS T1 OUTER APPLY
 (SELECT ' + @sql +
' FROM myMEETINGS M INNER JOIN
      (SELECT MeetingID, TAB, CODE, ROW_NUMBER() OVER (PARTITION BY MeetingID ORDER BY ID) rn
       FROM myCODES
      ) C ON C.MeetingID=M.ID 
  WHERE ID=T1.ID
 ) T2
WHERE T1.RaceDate=''2010-03-23'''

exec (@sql)

GO

DROP TABLE myCODES
DROP TABLE myMEETINGS

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chudmarekAuthor Commented:
Great Solution, I would have given you 10 times the points if i could have!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.