[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 216
  • Last Modified:

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.
0
kj96ee
Asked:
kj96ee
  • 2
  • 2
1 Solution
 
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
 
kj96eeAuthor Commented:
Thank you!
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now