flattening out multiple rows

Could somebody please help a beginner out with this one!  

B has a many-to-one relationship to A,

so this query

select
a.name,
b.date

from a

join b
on a.name = b.name

would normally return
name1,date1
name1,date2
name2,date1
etc.  

Instead,
I want to return a flat file looking like this:

name1,date1,date2,,,,
name2,date1,,,,,

 

Up to 6 dates in the flat file, and I need to have the dates in descending date order.  

Thanks.
kj96eeAsked:
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.

Patrick MatthewsCommented:
For a maximum of 6 dates per name, and assuming that for any given name each date is distinct...


CREATE PROCEDURE dbo.MakeTheList AS BEGIN

	SELECT a.[name], b1.date,
		(SELECT COUNT(1) FROM b b2 WHERE b2.[name] = a.[name] AND b2.date <= b1.date) AS Ordinal
	INTO #tmp
	FROM a LEFT JOIN
		b b1 ON a.[name] = b1.[name]
	ORDER BY a.[name], b1.date

	SELECT t1.[name],
		(SELECT t2.date FROM #tmp t2 WHERE t2.[name] = t1.[name] AND t2.ordinal < 2) AS Date1,
		(SELECT t2.date FROM #tmp t2 WHERE t2.[name] = t1.[name] AND t2.ordinal = 2) AS Date2,
		(SELECT t2.date FROM #tmp t2 WHERE t2.[name] = t1.[name] AND t2.ordinal = 3) AS Date3,
		(SELECT t2.date FROM #tmp t2 WHERE t2.[name] = t1.[name] AND t2.ordinal = 4) AS Date4,
		(SELECT t2.date FROM #tmp t2 WHERE t2.[name] = t1.[name] AND t2.ordinal = 5) AS Date5,
		(SELECT t2.date FROM #tmp t2 WHERE t2.[name] = t1.[name] AND t2.ordinal = 6) AS Date6
	FROM #tmp t1
	GROUP BY t1.[name]
	ORDER BY t1.[name]

	DROP TABLE #tmp

END

GO

EXEC dbo.MakeTheList

GO

Open in new window

0
kj96eeAuthor Commented:
Thank you!
I neglected to point out that the dates could be identical.  If the 7th is the same as the 6th, I would still just need the last 6 in order from most recent to oldest.  
0
Patrick MatthewsCommented:
CREATE PROCEDURE dbo.MakeTheList AS BEGIN

      SELECT IDENTITY(int, 1, 1) AS ID, a.[name], b.date
      INTO #tmp1
      FROM a LEFT JOIN
            b ON a.[name] = b.[name]
      ORDER BY a.[name], b.date

      SELECT t1.[name], t1.date,
            (SELECT COUNT(1) FROM #tmp1 t2 WHERE t2.[name] = t1.[name] AND t2.ID <= t1.ID) AS Ordinal
      INTO #tmp2
      FROM #tmp1 t1

      SELECT t1.[name],
            (SELECT t2.date FROM #tmp2 t2 WHERE t2.[name] = t1.[name] AND t2.ordinal < 2) AS Date1,
            (SELECT t2.date FROM #tmp2 t2 WHERE t2.[name] = t1.[name] AND t2.ordinal = 2) AS Date2,
            (SELECT t2.date FROM #tmp2 t2 WHERE t2.[name] = t1.[name] AND t2.ordinal = 3) AS Date3,
            (SELECT t2.date FROM #tmp2 t2 WHERE t2.[name] = t1.[name] AND t2.ordinal = 4) AS Date4,
            (SELECT t2.date FROM #tmp2 t2 WHERE t2.[name] = t1.[name] AND t2.ordinal = 5) AS Date5,
            (SELECT t2.date FROM #tmp2 t2 WHERE t2.[name] = t1.[name] AND t2.ordinal = 6) AS Date6
      FROM #tmp2 t1
      GROUP BY t1.[name]
      ORDER BY t1.[name]

      DROP TABLE #tmp1
      DROP TABLE #tmp2

END

GO

EXEC dbo.MakeTheList

GO
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
kj96eeAuthor Commented:
Thank you!
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 2005

From novice to tech pro — start learning today.