Results 1 to 11 of 11

Thread: Loop or Arrays

  1. #1
    Join Date
    Nov 2003
    Location
    South Africa
    Posts
    71

    Unanswered: Loop or Arrays

    See Attachment
    Attached Files Attached Files

  2. #2
    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. #3
    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. #4
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    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. #6
    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.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    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. #8
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    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. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Damn....

    very nice...

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

    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    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
  •