Create table query 1

[puppet]

I have a table with the following format:

Item Code_Type Code
---------------------
1 ABC H
1 DEF Q
1 GHI 20
25 ABC Y
25 DEF D
25 HGI 30
30 ABC L
30 DEF Q
30 GHI 25
30 GHI 45

And I want to create a flat table with the following format...

Item ABC DEF GHI
-------------------------
1 H Q 20
25 Y D 30
30 L Q 25
30 L Q 35

This would be simple if the Code_Type could only contain one value but at least one of the code types will sometimes (not always) contain multiple values - eg Code_Type GHI above has a value of 25 and 35. The actual data is different this was just an easier way to explain it.

I get table 1 from a dts package every morning and need to setup a job to create table 2 from table 1. I'm sure there must be an easy way to do this but I am still learning SQL and do not know how to do it.

Any ideas appreciated.

 

[bperry]

I'm sure there must be an easy way to do this

Don't be too hard on yourself. This doesn't seem easy to me. (Or probabloy it is, and I am too dumb to see it!)

Items 1 and 25 can be dealt with.
Item 30 seems impossible to me. It's like we are saying "read the first 3. Concatenate L+Q+25. Now remember the first 2 L+Q. Move to the next one. Concatenate L+Q+35."

In other words, the 'formula' needed to figure out the result is not clear at all. Want to explain some more?

bperry

 

[puppet]

Thinking about it more I guess I could live with - (or it may even be better) a table with the following format.

Item ABC DEF GHI1 GHI2 GHI3
----------------------------------------
1 H Q 20
25 Y D 30
30 L Q 25 35

Would this be easier?

 

[bperry]

Okay. That would be a little better.
At the root of the problem is, can we guarantee that there will ever be only a maximum of n columns? The sample data showing here has four columns, but the last row has five. Is five the most we can have? Six? Unknown?

What we are talking about here is known as pivoting, or crosstabs: basically swapping rows for columns. If the maximum columns can be set to some number, then there's something we can do here. If the maximum is unknown, then there's more trouble here than we want.

Do you see what I mean? To have columns in a Select statement, we basically need to have:
Select Col1, col2, col3, colN....
But if we don't know how many columns we might have, it makes it pretty tough to write the Select.

(In my experience, whenever someone says "5 is the most we can have", then you can bet the next day there will be 8.)

I'll wait to get some more comments from you. In the meantime, here's someone who has already invented the wheel. It's an interesting solution to this situation, and you might want to check it out.

http://www.rac4sql.net/rac_sql2k_main.asp

bperry

 

[puppet]

Yes there will be a known limit, we will only ever have a set number of code_types - at present 8 - and only two of those types allow multiple codes. Those two code_types that allow multiple values only allow a maximum of three different values. Hence GHI1, GHI2 and GHI3.

 

[bperry]

Okay, I see.

We will only ever have a set number of code_types - at present 8 -

I see from your output that you need 1 row per item; what we need to nail down is how many columns any single item could have. Can I take it that an item can therefore have up to 8 codes assigned to it? ie.
[tt]
1 ABC
1 DEF
1 CAR
1 FAR
1 BAR
1 TAR
1 JAR
1 XAR
[/TT]

Also, can two of those 8 (?) codes be the two that have a mzx of three values attached? i.e. So it seems like the maximum columns for one item could be 6 codes + 1x3 values + 1x3 values = 12 columns total?

i.e. at the bottom of any solution here is going to be a temporary table that has X columns for each item, so we need to know how many columns to build that thing with. (And the fewer columns we can have, the easier this will be.)

I see your original sample data has two items with 3 codes, and one item with 4, but I didn't know if I could count on that as being typical or not.

bperry

 

[bperry]

Hi again,
I've got a couple of solutions brewing in my mind.

I was thinking your might want to provide the real list of 8 codes, so I can call these column headings the correct names (i.e. instead of ABC, DEF, which I imagine you used for ease of explanation.) Also indicate which two of those codes can have the multiple occurences (up to 3, i think you said.)

 

[puppet]

Yes 12 columns total - not all items will have an entry for every column though - in fact more often than not the IC-READ and IC-PLIST will only have one value - but sometimes they will have two or three. Below is an example of an item with three PLIST codes and two READ codes.
I5I I5ICT I5ICC
-------------------- -------- --------
0822013525 IC-BISAC STU02600
0822013525 IC-DSKCD Z
0822013525 IC-PAGES 58
0822013525 IC-PLIST Y
0822013525 IC-PLIST R
0822013525 IC-PLIST J
0822013525 IC-READ Z
0822013525 IC-READ Q
0822013525 IC-SLI E5
0822013525 IC-PV T2HU
0822013525 IC-SER ID34

 

[bperry]

Two quick questions:

(1) SQL2000?

(2) Stored Procedure okay? (i.e. this can't be just a single Select statement)

 

[bperry]

If an item doesn't have a value (a code) for a particulat column, what value would you like to have for that column? NULL? blank? empty value?

 

[puppet]

SQL 7. Yes a stored proceedure would be fine as long as I can run it from a scheduled job. NULL values are fine.

 

[bperry]

Okay, this is running well on my system, so let's see if we can do the same on yours.

My solution uses two stored procedures. One calls the other. You will execute uspPuppet2, which will automatically call uspPuppet1 when it needs to.

The PLIST and READ codetypes are a headache; we have to break them down into separate codes. READ, READ2, READ3, etc. That's the job of uspPuppet1. (By the way, one of things I like about this solution is that it's not limited to just READ or PLIST being repeated. Any code can be repeated, any number of times, and this stuff should keep working.)

I called my original input file ITEMS. Yours is probably called something different. You can find it in uspPuppet1; that's the only place you should need to make any changes.

These things usually need a bit of fine tuning. (But it's a beautiful thing when they run first shot.) Let's see how it runs at your place and make any adjustments. Like I said, it works great here.

bperry

 

[bperry]

Here is uspPuppet1:

CREATE PROCEDURE uspPuppet1
As
SET NOCOUNT ON
-- Create a forward-only cursor to collect the data
--delete from ##TempItems
declare myCursor INSENSITIVE CURSOR FOR
select I5I, I5ICT, I5ICC
From Items Order By I5I, I5ICT
open myCursor

-- Create the local cursor variables needed
declare @I varchar(10)
declare @ICT varchar(10)
declare @ICC varchar(10)

-- Create other 'working' variables needed
declare @prevI varchar(10)
declare @prevICT varchar(10)
declare @outICT varchar(10)
declare @totCount int
declare @typeCount int

set @prevI = ''
set @prevICT = ''
set @outICT = ''
set @totCount = 0
set @typeCount = 2

-- Do the first Fetch on the cursor,
-- and then begin processing
Fetch myCursor into @I, @ICT, @ICC

while @@fetch_status = 0
BEGIN
-- Test for very first row fetched
IF @totcount = 0
Begin
set @outICT = @ICT
End
Else
Begin
-- see if Item has changed
If @I <> @prevI
Begin
set @typeCount = 2
set @outICT = @ICT
End
Else
Begin
-- See if the TypeCode has changed
If @ICT = @prevICT
Begin
set @outICT = @ICT +
CAST(@typeCount as varchar(3))
set @typecount = @typeCount + 1
End
Else
Begin
-- No changes needed
set @outICT = @ICT
set @typecount = 2
End
End
End

INSERT ##TempItems VALUES (@I, @outICT, @ICC)
set @prevI = @I
set @prevICT = @ICT
set @totCount = @totCount + 1

Fetch myCursor into @I, @ICT, @ICC
END

close myCursor
deallocate myCursor
GO

 

[bperry]

and here is uspPuppet2:
[tt]
CREATE PROCEDURE uspPuppet2
As
SET NOCOUNT ON
---------------------------------
-- We need an Items table
---------------------------------
create table ##TempItems (
PkId int Identity,
Item varchar(10),
Type varchar(10),
Code varchar(10)
)
-- Now run procedure to fill ##TempItems
EXEC usppuppet1

----------------------------------------
-- Create a temp table holding all the
-- different Type Codes we will have
----------------------------------------
CREATE TABLE #TempTypes (Type varchar(10))
INSERT into #TempTypes
Select Type From ##TempItems

declare @typeCount int
select @typeCount = COUNT(*) from #TempTypes
-----------------------------------------------
declare @Q char(1)
set @Q = CHAR(39)

declare @sqlColumns varchar(8000)
set @sqlColumns = ''

declare @type varchar(10)
select @type = MIN(Type) From #TempTypes

-- This builds the section of our SQL statement
-- that outputs each column
While @type IS NOT NULL
BEGIN
set @sqlColumns = @sqlColumns +
', MAX((CASE WHEN Type=' + @Q + @type + @Q + 'Then Code Else Null END)) as ' + @Q + @type + @Q

select @type = MIN(Type)
From #TempTypes where Type > @type
END

declare @sqlSelect varchar(8000)
set @sqlSelect = 'Select Item'

declare @sqlFrom varchar(8000)
set @sqlFrom = ' From ##TempItems Group By Item Order by Item'

declare @sqlAll varchar(8000)
set @sqlAll = @sqlSelect + @sqlColumns + @sqlFrom

-- Finally! Execute our final SQL Select statement
EXEC (@sqlAll)

-- Ditch our temporary tables
Drop table ##TempItems
Drop table #TempTypes
GO
[/tt]
-------------------

 

[puppet]

!!!! I was expecting a simple stored proceedure not a masterpiece!!!!

This works a treat! It returns 21430 rows in 69 seconds and looks exactly like I wanted.

I need this to create a permenant table though and I can't figure out how to modify the proceedure to do this.

 

[bperry]

create table ##TempItems (
PkId int Identity,
Item varchar(10),
Type varchar(10),
Code varchar(10)
)

in uspPuppet2, near the top, the table you are talking about is ##TempItems. ## is what creates it as a 'global temporary' table. Drop ## from the name, and it becomes permanent.

You probably also want to delete or comment out the &quot;drop table ##TempItems at the bottom.

 

[bperry]

Oh, I see, I think I misunderstood you.
When you run uspPuppet2, instead of a resultset being returned back to you, you want the records dumped into a permanent table. Is that right?

If that's the case, forget what I said about ##TempItems table. That's just a working table, not the one you want to keep permanently.

Once you confirm this is what you're thinking, I'll suggest a change.

The next time you run upsPuppet2, what about all the rows already in our new permanent table (from the first run)? Clear them out first on any subsequent re-runs?

bperry

 

[puppet]

Yep the records need to be returned into a permenant table which should be dropped or cleared each time.

 

[bperry]

Here is a revised SP upsPuppet2 that seems to do what you need. It's working well on my system. I've named the table CodeSummary; it's mentioned in a few spots here, so you need to make changes to a name that suits you.
At the very end there's a statement to Index the new table on Item; if you don't that index to support any other queries you might be running against CodeSummary, then delete that statement.
-------------------------
CREATE PROCEDURE uspPuppet2
As
SET NOCOUNT ON
-------------------------------------------------
-- Ditch permanent table, if it exists
-------------------------------------------------
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'CodeSummary')
DROP TABLE CodeSummary

----------------------------------------------------
-- We need an temporary Items table
----------------------------------------------------
create table ##TempItems (
PkId int Identity,
Item varchar(10),
Type varchar(10),
Code varchar(10)
)
-- Now run procedure to fill ##TempItems
EXEC usppuppet1

-----------------------------------------------------------
-- Create a temp table holding all the
-- different Type Codes we will have
-----------------------------------------------------------
CREATE TABLE #TempTypes (Type varchar(10))
INSERT into #TempTypes
Select DISTINCT Type From ##TempItems

declare @typeCount int
select @typeCount = COUNT(*) from #TempTypes
----------------------------------------------------------
declare @Q char(1)
set @Q = CHAR(39)

declare @sqlColumns varchar(8000)
set @sqlColumns = ''

declare @type varchar(10)
select @type = MIN(Type) From #TempTypes

-- This builds the section of our SQL statement
-- that outputs each column
While @type IS NOT NULL
BEGIN
set @sqlColumns = @sqlColumns +
', MAX((CASE WHEN Type=' + @Q + @type + @Q + ' Then Code Else Null END)) as ' + @Q + @type + @Q
select @type = MIN(Type) From #TempTypes where Type > @type
END

declare @sqlSelect varchar(8000)
set @sqlSelect = 'Select Item'

declare @sqlFrom varchar(8000)
set @sqlFrom = ' INTO CodeSummary From ##TempItems Group By Item Order by Item'

declare @sqlAll varchar(8000)
set @sqlAll = @sqlSelect + @sqlColumns + @sqlFrom

-- Execute our final SQL Select statement
-- to create and fill the CodeSummary table
EXEC (@sqlAll)

-- Ditch our temporary tables
Drop table ##TempItems
Drop table #TempTypes

CREATE UNIQUE INDEX ItemIndex on CodeSummary(Item)
GO

 

[puppet]

Thanks for all your help bperry. This seems to run perfectly except for a warning that I get:

Warning: Null value eliminated from aggregate.

Not quite sure where this is referring to and the data looks to be fine to me.