# Thread: Loop or Arrays

1. Registered User
Join Date
Nov 2003
Location
South Africa
Posts
71

## Unanswered: Loop or Arrays

See Attachment

2. Registered User
Join Date
Aug 2004
Posts
8
What 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

3. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Provided Answers: 54
I take it that my first answer didn't work?

-PatP

4. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
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

Code:
```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```

5. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Provided Answers: 54
I tidied up some minor syntax problems, but it seems to work for me if I use:
Code:
```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.StoryID```
Am I missing something?

-PatP

6. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
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.

7. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Provided Answers: 54
Your code seems to have exactly the same data as his text file. Am I confused?

-PatP

8. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
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?

9. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Provided Answers: 54
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!
Code:
```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

10. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
Damn....

very nice...

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

11. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Provided Answers: 54
Hey, you do good work... Just for that, you can take the rest of the day off!

-PatP

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•