Create fieldnames of table within stored procedure

• melanbe

  SSC Enthusiast

  Points: 194

  More actions

  November 17, 2005 at 9:42 am

  #169612

  I am creating a stored procedure that takes a flat file and after a little massaging puts the data into a table. Here is what the flat file looks like:

  ID|IMAGE|THUMBNAIL 10050335|http://www.anysite.com/images/35/10050335_101.JPG|http://www.anysite.com/images/35/10050335_101.JPG 10050383|http://www.anysite.com/images/83/10050383_101.JPG|http://www.anysite.com/images/83/10050383_101.JPG

  C030221532|http://www.anysite.com/images/32/C030221532_101.JPG|http://www.anysite.com/images/32/C030221532_101.JPG C030221532|http://www.anysite.com/images/32/C030221532_201.JPG|http://www.anysite.com/images/32/C030221532_201.JPG C030221532|http://www.anysite.com/images/32/C030221532_501.JPG|http://www.anysite.com/images/32/C030221532_501.JPG

  I want the new table to have structure of:

  ID

  Image1

  Thumbnail1

  Image2

  Thumbnail2 ....

  Basically I want 1 row for each id number. There can be up to 15 photos and thumbnails for each id.

  In the stored procedure I was trying to build the fieldname based on how many times I inserted an image/thumbnail for that Id i.e. fieldname = image+cnt for the insert/ update statement and that doesn't seem to work in my code.

  Am I on the right track or if not could you point me to the right direction.

• David A. Long

  SSCrazy Eights

  Points: 8421

  More actions

  November 18, 2005 at 4:47 am

  #604594

  Why create a flat file table that will be very hard to maintain? Here is a better design alternative

  CREATE TABLE #Pics (ImageID int identity, ID varchar(10)

          , Picture varchar(256), Thumbnail varchar(256))

  INSERT INTO #Pics (ID, Picture, Thumbnail)

  SELECT '10050335', 'http://www.anysite.com/images/35/10050335_101.JPG'

          , 'http://www.anysite.com/images/35/10050335_101.JPG' UNION ALL

  SELECT '10050383', 'http://www.anysite.com/images/83/10050383_101.JPG'

          , 'http://www.anysite.com/images/83/10050383_101.JPG' UNION ALL

  SELECT 'C030221532', 'http://www.anysite.com/images/32/C030221532_101.JPG'

          , 'http://www.anysite.com/images/32/C030221532_101.JPG' UNION ALL

  SELECT 'C030221532', 'http://www.anysite.com/images/32/C030221532_201.JPG'

          , 'http://www.anysite.com/images/32/C030221532_201.JPG' UNION ALL

  SELECT 'C030221532', 'http://www.anysite.com/images/32/C030221532_501.JPG'

          , 'http://www.anysite.com/images/32/C030221532_501.JPG'

  SELECT * FROM #Pics

  SELECT O.ID

          , MIN(CASE O.Rank WHEN 1 THEN O.Picture ELSE NULL END) AS Picture1

          , MIN(CASE O.Rank WHEN 1 THEN O.Thumbnail ELSE NULL END) AS Thumbnail1

          , MIN(CASE O.Rank WHEN 2 THEN O.Picture ELSE NULL END) AS Picture2

          , MIN(CASE O.Rank WHEN 2 THEN O.Thumbnail ELSE NULL END) AS Thumbnail2

          , MIN(CASE O.Rank WHEN 3 THEN O.Picture ELSE NULL END) AS Picture3

          , MIN(CASE O.Rank WHEN 3 THEN O.Thumbnail ELSE NULL END) AS Thumbnail3

          , MIN(CASE O.Rank WHEN 4 THEN O.Picture ELSE NULL END) AS Picture4

          , MIN(CASE O.Rank WHEN 4 THEN O.Thumbnail ELSE NULL END) AS Thumbnail4

          -- add case statements for the total number of columns

  FROM (SELECT TOP 100 PERCENT ID

                  , Picture, Thumbnail, Rank = (SELECT COUNT(*) FROM #Pics R

                          WHERE I.ID = R.ID AND R.ImageID < I.ImageID)+1

          FROM #Pics I ORDER BY ID, Rank) O

  GROUP BY O.ID

  DROP TABLE #Pics

  Andy