How to convert 2 coded fields to 6 uncoded and insert data into table? 1

[uerobertson]

Hi,
I am writing a stored procedure to convert our client's data and move it into our database. I am using SQLSvr 2000 and Win2000.

I have a table containing 3 fields:

Table1

ID | Date | Code
-----------------
1 | Jan 1 | OD1
-----------------
1 | Feb 1 | SD1
-----------------
1 | Mar 1 | OD2
-----------------
1 | Apr 1 | SD2
-----------------
1 | May 1 | OD3
-----------------
1 | Jun 1 | SD3
-----------------
2 | Jul 1 | OD1
-----------------
2 | Aug 1 | SD1
-----------------
2 | Sep 1 | OD2
-----------------
2 | Oct 1 | SD2
-----------------
2 | Nov 1 | OD3
-----------------
2 | Dec 1 | SD3
-----------------
. . .
. . .
. . .

I would like to directly insert this data into another table and end up with this:

Table2

ID | oDate1 | sDate1 | oDate2 | sDate2 | oDate3 | sDate3
---------------------------------------------------------
1 | Jan 1 | Feb 1 | Mar 1 | Apr 1 | May 1 | Jun 1
---------------------------------------------------------
2 | Jul 1 | Aug 1 | Sep 1 | Oct 1 | Nov 1 | Dec 1
---------------------------------------------------------
etc.

(Please excuse the rather inane sample data)

What is the best way of doing this in an insert statement? I have been working on this for some time and so far have only disconnected scribbling.

Thanks for your help.

U.

 

[JohnDTampaBay]

Is there a maximum number of O dates and S dates or is that unknown? You only show 3 of each in your example. Should the dates appear in the columns chronologically as in the example or does it matter?

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[uerobertson]

There are 6 types of dates only but no maximum number of records and ~78,000 different ID's (~468,000 records in Table 1).

The dates themselves are completely random. (I knew I should have put in different sample data. I did it that way to try to make it obvious where the dates go in the new table).

Thanks,
Ursula

 

[lyudmila]

Did you try to use dynamic SQL?

 

[uerobertson]

Do you have an example of what you mean by 'dynamic SQL'?

U.

 

[JohnDTampaBay]

Dynamic SQL is a SQL statement that is built and executed on the fly. See below.

So an ID could have 3 O dates and 29 S dates and 0 X dates and 7 Y dates etc.? Yuck! Is this a one time conversion or a periodic import?

If its a one time conversion, you'll need to figure out the maximum number for each date time and create the appropriate number of columns. If its a periodic import, then you'll need to check the number of columns for each date type before import.

You will probably need some dynamic SQL to handle the insertion. You could hard code all of the SELECTs but it would be a long, ugly procedure. Something like this maybe:

TRUNCATE TABLE Table2

INSERT Table2 (ID)
SELECT DISTINCT ID
FROM Table1

DECLARE @OCount int, @cntr int, @sql nvarchar(1000)
SET @OCount = [i]Maximum number of O dates[/i]
SET @cntr = 1

WHILE @cntr <= @OCount BEGIN
  SET @sql = 'UPDATE Table2 SET ODate'
             + CAST(@cntr AS varchar)
             + ' = t1.[Date] FROM Table2 t2 '
             + 'INNER JOIN Table1 t1 ON t1.ID = t2.ID '
             + 'WHERE t1.Code = ''OD'
             + CAST(@cntr AS varchar)
             + ''''

--  EXEC(@sql)
  PRINT(@sql)
  SET @cntr = @cntr + 1
END

Try running the code snippet above and check out the SQL it produces. Modify it as necessary. When the individual SQL statements appear correct, uncomment the EXEC line and let it fly. Good luck!


--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[sunila7]

Angel's answer should give what u want, but I just wanted to see whether this SQL is what u r looking for....

Select ID,
CASE Code when 'OD1' then Date End oDate1,
CASE Code when 'SD1' then Date End sDate1,
CASE Code when 'OD2' then Date End oDate2,
CASE Code when 'SD2' then Date End sDate2,
CASE Code when 'OD3' then Date End oDate3,
CASE Code when 'SD3' then Date End sDate3
FROM TBL Group by ID

Hope it helps

Sunil

 

[uerobertson]

Thanks for the code snippet. I will experiment and let you know. The lightbulb has finally gone on.

BTW: We seem to be miscommunicating about the dates. There are only 6 dates. There are three oDates (order dates) and 3 sDates (shipping dates). But I think I have to write the code now.

Thanks,
Ursula

(PS. I do know what Dynamic SQL is, I have used it many times. I was looking for ideas on how to design the algorithm.

 

[uerobertson]

Thanks Sunil.

I didn't know you could use a case statement inside a select. That's perfect.

Ursula.

 

[uerobertson]

Hi,
Got it to work. In case anyone is interested, here is the code:

DROP TABLE  #dateTable
SELECT [IIC Model Number],  
CASE [Code of Date] WHEN 'POD' THEN [Date] END oDate1,
CASE [Code of Date] WHEN 'PD2' THEN [Date] END sDate1,
CASE [Code of Date] WHEN 'MOD' THEN [Date] END oDate2,
CASE [Code of Date] WHEN 'MD2' THEN [Date] END sDate2,
CASE [Code of Date] WHEN 'DT3' THEN [Date] END oDate3,
CASE [Code of Date] WHEN 'D32' THEN [Date] END sDate3
INTO #dateTable
FROM INAAdmin.AISDAD06 GROUP BY [IIC Model Number], 
[Code of Date], [Date]

It's putting it into 6 different records though, trying to get it into one record by ID (aka IIC Model Number).

I found out this is a pivot function. Should have known that one. :~)

Ursula