Loop or Arrays

See AttachmentWhat about This?

insert into #Maggy
(id,AnnouncementMessageText1, AnnouncementMessageText2, AnnouncementMessageText3, AnnouncementMessageText4)
select
id = a.storyID,
AnnouncementMessageText1 = a1.Text,
AnnouncementMessageText2 = a2.Text,
AnnouncementMessageText3 = a3.Text,
AnnouncementMessageText4 = a4.Text
from
(select storyID from TableA group by storyID) a
left join tableA a1 on a1.storyID = a.StoryID and a1.LineNumber = 1
left join tableA a2 on a2.storyID = a.StoryID and a2.LineNumber = 2
left join tableA a3 on a3.storyID = a.StoryID and a3.LineNumber = 3
left join tableA a4 on a4.storyID = a.StoryID and a4.LineNumber = 4
order by a.storyID|||I take it that my first answer (http://www.dbforums.com/t1008761.html) didn't work?

-PatP|||You have to look at his sample data...some one should say that this is a presentation layer problem...but it was a fun exercise

USE Northwind
GO

CREATE TABLE Numbers(Col1 int IDENTITY(1,1), Col2 int)
GO
SET NOCOUNT ON
DECLARE @.x int
SELECT @.x = 1

WHILE @.x < 100
BEGIN
INSERT INTO Numbers(Col2)
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4

SELECT @.x = @.x + 1
END
SET NOCOUNT OFF
GO

CREATE TABLE myTable99(StoryID int, LineNumber int, [Text] varchar(7000))
GO

INSERT INTO myTable99(StoryID,LineNumber,[Text])
SELECT 33743, 1, 'GRT ' UNION ALL
SELECT 33743, 2, 'Growthpoint - Audited Results For The Year Ended 30 June 2004 ' UNION ALL
SELECT 33743, 3, 'GROWTHPOINT PROPERTIES LIMITED ' UNION ALL
SELECT 33743, 4, '(Registration number 1987/004988/06) ' UNION ALL
SELECT 33743, 5, 'Share code GRT ISIN: ZAE000037669 ' UNION ALL
SELECT 33743, 6, '("Growthpoint" or "the company") ' UNION ALL
SELECT 33743, 7, '* 3,7% increase in distribution * market capitalisation ' UNION ALL
SELECT 33743, 8, ' to 69,0 cents in excess of R3,7 billion ' UNION ALL
SELECT 33743, 9, '* property assets exceed * largest SA company listed in ' UNION ALL
SELECT 33743, 10, ' R6,6 billion Real Estate sector of the JSE ' UNION ALL
SELECT 33743, 11, '* improved liquidity and tradeability * vacancies down to 4,7% ' UNION ALL
SELECT 33743, 12, 'AUDITED RESULTS FOR THE YEAR ENDED 30 JUNE 2004 ' UNION ALL
SELECT 33743, 13, 'CONDENSED CONSOLIDATED INCOME STATEMENT ' UNION ALL
SELECT 33743, 14, ' 2004 2003 ' UNION ALL
SELECT 33743, 15, ' R"000 R"000 ' UNION ALL
SELECT 33743, 16, 'Revenue 920 457 452 982 ' UNION ALL
SELECT 33743, 17, 'Property expenses (314 141) (158 775) ' UNION ALL
SELECT 33743, 18, 'Net property income 606 316 294 207 ' UNION ALL
SELECT 33743, 19, 'Other operating expenses (34 887) (13 533) ' UNION ALL
SELECT 33743, 20, 'Net property income after other operating expenses 571 429 280 674 ' UNION ALL
SELECT 33744, 1, 'GRT ' UNION ALL
SELECT 33744, 2, 'Growthpoint - Audited Results For The Year Ended 30 June 2004 ' UNION ALL
SELECT 33744, 3, 'GROWTHPOINT PROPERTIES LIMITED ' UNION ALL
SELECT 33744, 4, '(Registration number 1987/004988/06) ' UNION ALL
SELECT 33744, 5, 'Share code GRT ISIN: ZAE000037669 ' UNION ALL
SELECT 33744, 6, '("Growthpoint" or "the company") ' UNION ALL
SELECT 33744, 7, '* 3,7% increase in distribution * market capitalisation ' UNION ALL
SELECT 33744, 8, ' to 69,0 cents in excess of R3,7 billion ' UNION ALL
SELECT 33744, 9, '* property assets exceed * largest SA company listed in ' UNION ALL
SELECT 33744, 10, ' R6,6 billion Real Estate sector of the JSE ' UNION ALL
SELECT 33744, 11, '* improved liquidity and tradeability * vacancies down to 4,7% ' UNION ALL
SELECT 33744, 12, 'AUDITED RESULTS FOR THE YEAR ENDED 30 JUNE 2004 ' UNION ALL
SELECT 33744, 13, 'CONDENSED CONSOLIDATED INCOME STATEMENT ' UNION ALL
SELECT 33744, 14, ' 2004 2003 ' UNION ALL
SELECT 33744, 15, ' R"000 R"000 '
GO

SELECT a.storyid, a.Linenumber
, AnnouncementMessageText1
, AnnouncementMessageText2
, AnnouncementMessageText3
, AnnouncementMessageText4
FROM ( SELECT StoryId, [Text] AS AnnouncementMessageText1, LineNumber
FROM myTable99 JOIN Numbers ON LineNumber = Col1 AND Col2 = 1) AS a
LEFT JOIN ( SELECT StoryId, [Text] AS AnnouncementMessageText2, LineNumber
FROM myTable99 JOIN Numbers ON LineNumber = Col1 AND Col2 = 2) AS b
ON a.StoryId = b.StoryId AND b.LineNumber = a.LineNumber + 1
LEFT JOIN ( SELECT StoryId, [Text] AS AnnouncementMessageText3, LineNumber
FROM myTable99 JOIN Numbers ON LineNumber = Col1 AND Col2 = 3) AS c
ON a.StoryId = c.StoryId AND c.LineNumber = a.LineNumber + 2
LEFT JOIN ( SELECT StoryId, [Text] AS AnnouncementMessageText4, LineNumber
FROM myTable99 JOIN Numbers ON LineNumber = Col1 AND Col2 = 4) AS d
ON a.StoryId = d.StoryId AND d.LineNumber = a.LineNumber + 3
ORDER BY a.storyid, a.LineNumber
GO

DROP TABLE myTable99
DROP TABLE Numbers
GO|||I tidied up some minor syntax problems, but it seems to work for me if I use:SELECT a.StoryID
, Min(CASE WHEN 1 = a.LineNumber THEN a.[Text] END) AS [Name]
, Min(CASE WHEN 2 = a.LineNumber THEN a.[Text] END) AS [Surname]
, Min(CASE WHEN 3 = a.LineNumber THEN a.[Text] END) AS [Policy]
, Min(CASE WHEN 4 = a.LineNumber THEN a.[Text] END) AS [Date]
FROM myTable99 AS a
GROUP BY a.StoryIDAm I missing something?

-PatP|||Did you look at the sample data or cut and paste my code?

His line numbers are sequential from 1 to n.

They seem to identify the line number in a report.|||Your code seems to have exactly the same data as his text file. Am I confused?

-PatP|||Pronoun trouble.

Look I ran your code...and I only get 2 rows.....

Did you run my code?

Plus I think you've got 2 threads mixed up together...

His first "story" has 20 lines in it...what are you proposing for line #'s over 4?

Damn, I need a drink.

Are you playing with me?|||Wait a second! I think the light just went on... He wants to group the rows by groups of four, starting each story on a new line ?!?! Well why the heck didn't he say so!SELECT c.StoryID
, c.LineNumber
, Min(CASE WHEN 0 + c.LineNumber = a.LineNumber THEN a.Text END)
, Min(CASE WHEN 1 + c.LineNumber = a.LineNumber THEN a.Text END)
, Min(CASE WHEN 2 + c.LineNumber = a.LineNumber THEN a.Text END)
, Min(CASE WHEN 3 + c.LineNumber = a.LineNumber THEN a.Text END)
FROM (SELECT b.StoryID, b.LineNumber
FROM TableA AS b
WHERE 1 = b.LineNumber % 4) AS c
JOIN TableA AS a
ON (a.StoryID = c.StoryID)
GROUP BY c.StoryID, c.LineNumber
ORDER BY c.StoryID, c.LineNumber-PatP|||Damn...

very nice...

I was so proud of my code.....

:(|||Hey, you do good work... Just for that, you can take the rest of the day off!

-PatP