Select / transpose a single rows values into multiple columns 1

[djhawthorn]

Hello, I have a single row (or rather tons of rows with the same values) like this:

page 1, toys, page 2, sporting, page 3, auto, page 4, manchester, page 5, unused, page 6, unused, page 7, unused

(There are other non-same values in these rows, but that is unimportant).

I need to seperate one of these rows into multiple columns - ie. the single row above would end up creating multiple rows in another table, like so:

page 1, toys
page 2, sporting
page 3, auto
page 4, manchester
page 5, unused
page 6, unused
page 7, unused

How do I go about doing that with a SELECT INTO statement?

MCSE NT4/W2K

 

[gmmastros]

Here's one method. It uses a UDF, so try not to use it too often since performance will be a little slower than you would appreciate.

You will need a UDF to use my method.

CREATE  Function Split(@CommaDelimitedFieldNames Varchar(8000),@SplitChar Char(1))  
Returns @Tbl_FieldNames Table  (FieldName VarChar(100))  As  

Begin 
 Set @CommaDelimitedFieldNames =  @CommaDelimitedFieldNames + @SplitChar

 Declare @Pos1 Int
 Declare @pos2 Int
 
 Set @Pos1=1
 Set @Pos2=1

 While @Pos1<Len(@CommaDelimitedFieldNames)
 Begin
  Set @Pos1 = CharIndex(@SplitChar,@CommaDelimitedFieldNames,@Pos1)
  Insert @Tbl_FieldNames Select  Cast(Substring(@CommaDelimitedFieldNames,@Pos2,@Pos1-@Pos2) As VarChar(100))
  Set @Pos2=@Pos1+1
  Set @Pos1 = @Pos1+1
 End 
 Return
End

Here's an example of how you would use it...

Declare @Original VarChar(1000)
set @Original = 'page 1, toys, page 2, sporting, page 3, auto, page 4, manchester, page 5, unused, page 6, unused, page 7, unused'

Declare @Temp 
Table   (RowId Integer Identity(1,1), 
        Data VarChar(100))

Insert Into @Temp(Data)
Select FieldName From dbo.Split(@Original, ',')

Select LTrim(A.Data + ',' + B.Data)
From   @Temp A
       Inner join @Temp B 
         On  A.RowId = B.RowId - 1
         And A.RowId % 2 = 1

Normally, the split function will take a comma delimited string and create records from them. In your case, you want every other comma to seperate the records. The split function originally creates twice as many records, but the last select combines records on consecutive rows to produce the output you are looking for.

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[djhawthorn]

Sorry; I didn't make myself clear... (my fault)

The data is already in a temporary SQL table - it was in a CSV file, which I've imported into a SQL table to make it easier - the data needs to be broken up from that single temporary table into multiple tables (using a DTS package I'm making). This transposing is only part of the job.

There are 32 "pages" in total - this never changes, if that helps.

For example, the data appears like this (with column names this time)

Page1   Page1Category    Page2   Page2Category   Page3    Page3Category
------- ---------------- ------- --------------- -------- --------------
1       toys             2       sporting        3        auto

etc.

This needs to become

PageNumber    PageDetail
------------- --------------
1             toys
2             sporting
3             auto

etc..

Hope that makes it more clear.

MCSE NT4/W2K

 

[djhawthorn]

I should also note, I need the PageNumber extracted and inserted into the table, as well as the PageDetail. So for example, if they decide to change the PageNumber from "1, 2, 3.." etc to "One, Two, Three.." etc, I don't need to touch any code.

MCSE NT4/W2K

 

[gmmastros]

Just curious here...

Will the value for the Page1 field always be 1, and the value for the Page2 field always be 2, etc.. If this is true, whew... talk about bad structure.

Anyway... If I understand you correctly, you start off with a table like this....

Declare @Temp Table (IdNum Integer, Page1 integer, Page1Category VarChar(50), Page2 Integer, Page2Category VarChar(50), Page3 Integer, Page3Category VarChar(20))

Insert Into @Temp
Select 1,1,'toys',2,'sporting',3,'auto'
Union all Select 2,1,'planes',2,'trains',3,'automobiles'

Select * From @Temp

I assume there are multiple records with some unique identitfier you want to preserve, so I added the IdNum field. To get your desired results...

Select IdNum, 1 As PageNumber, Page1Category As PageDetail From @Temp
Union All Select IdNum, 2, Page2Category From @Temp
Union All Select IdNum, 3, Page3Category From @Temp

I'm apologizae for not understanding your initial requirements. I hope this makes up for it.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Change the last select to...

Select IdNum, Page1 As PageNumber, Page1Category As PageDetail From @Temp
Union All Select IdNum, Page2, Page2Category From @Temp
Union All Select IdNum, Page3, Page3Category From @Temp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[djhawthorn]

In answer to your initial question? Yes, they will always be the same. I'm working with an existing structure - the CSV is an export from a very badly designed system... something that can't be changed.

I start off with a table like this:

CREATE TABLE [tempdb].[dbo].[tempCatalogueImport] (
        CatCode nvarchar(6),
        CatalogueTheme nvarchar(50), 
        CatalogueReleaseDate Datetime,
        CatStartDate Datetime,
        CatEndDate Datetime,
        PricingStartDate Datetime,
        PricingEndDate Datetime,
        DefaultCostingStartDate Datetime,
        DefaultCostingEndDate Datetime,
        Page1 Integer,
        Page1Category nvarchar(50),
        Page2 Integer,
        Page2Category nvarchar(50),
        Page3 Integer,
        Page3Category nvarchar(50),
        Page4 Integer,
        Page4Category nvarchar(50),
        Page5 Integer,
        Page5Category nvarchar(50),
        Page6 Integer,
        Page6Category nvarchar(50),
        Page7 Integer,
        Page7Category nvarchar(50),
        Page8 Integer,
        Page8Category nvarchar(50),
        Page9 Integer,
        Page9Category nvarchar(50),
        Page10 Integer,
        Page10Category nvarchar(50),
        Page11 Integer,
        Page11Category nvarchar(50),
        Page12 Integer,
        Page12Category nvarchar(50),
        Page13 Integer,
        Page13Category nvarchar(50),
        Page14 Integer,
        Page14Category nvarchar(50),
        Page15 Integer,
        Page15Category nvarchar(50),
        Page16 Integer,
        Page16Category nvarchar(50),
        Page17 Integer,
        Page17Category nvarchar(50),
        Page18 Integer,
        Page18Category nvarchar(50),
        Page19 Integer,
        Page19Category nvarchar(50),
        Page20 Integer,
        Page20Category nvarchar(50),
        Page21 Integer,
        Page21Category nvarchar(50),
        Page22 Integer,
        Page22Category nvarchar(50),
        Page23 Integer,
        Page23Category nvarchar(50),
        Page24 Integer,
        Page24Category nvarchar(50),
        Page25 Integer,
        Page25Category nvarchar(50),
        Page26 Integer,
        Page26Category nvarchar(50),
        Page27 Integer,
        Page27Category nvarchar(50),
        Page28 Integer,
        Page28Category nvarchar(50),
        Page29 Integer,
        Page29Category nvarchar(50),
        Page30 Integer,
        Page30Category nvarchar(50),
        Page31 Integer,
        Page31Category nvarchar(50),
        Page32 Integer,
        Page32Category nvarchar(50),
        ProductCode Text,
        SupplierCode Text,
        BrandCode Text,
        ProdCatDescription Text,
        ProductModel Text,
        ProductDescription Text,
        St Text,
        RegularInvoiceCostExGst Money,
        CostDeal Money,
        Rebate Decimal,
        BonusMonthly Money,
        BonusQuarterly Money,
        PageCategory nvarchar(50),
        PageNumber Integer,
        MarketText Text,
        CostSpecial Money,
        PowerPartner Money,
        SpecialDollarAllowance Money,
        NettInvoiceCostExGst Money,
        NettInvoiceCostIncGst Money,
        SellPrelimin Money,
        SellFinal Money,
        GrossProfitDollars Money,
        GrossProfitPercentage Decimal,
        StorePurchaseComments Text,
        StorePosComments Text,
        SupplierStatus Text,
        Label Bit,
        AdvTV Bit,
        AdvPress Bit,
        AdvRadio Bit,
        CostStartDate Datetime,
        CostEndDate Datetime,
        Custom1 Text,
        Custom2 Text,
        Custom3 Text,
        Custom4 Text,
        Custom5 Text
)

Which it gets the values imported from the CSV file. There are multiple CSV files which change over time; each one will be an addition to the database. Each row has exactly the same "page" detail listed; its (some of) the other fields that change between rows (so I only need one of the rows to form this new table).

The new table has four fields:

CatPages (CatPageID, CatId, PageNumber, PageDetail)

The CatPageID is a unique identity value - auto-generated (primary key).
The CatId is a foreign key.
The PageNumber and PageDetail I get from the CSV import (above).

I formed the following query based on your suggestion:

Select TOP 1 Page1 As PageNumber, Page1Category As PageDetail From @Temp
Union All Select TOP 1 Page2, Page2Category From @Temp
Union All Select TOP 1 Page3, Page3Category From @Temp
etc..

Does that look right?


MCSE NT4/W2K

 

[gmmastros]

Pretty close! I only have a couple points to make.

Your query is using my @Temp table variable, which I only used for demonstration purposes. You'll need to replace @Temp with your actual table name.

Also, you should not use the tempdb database. It's one of the system databases used by SQL Server.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[djhawthorn]

Thanks heaps, works like a charm!

MCSE NT4/W2K