Convert Row-based table to Column-based

Question

Post reply

Convert Row-based table to Column-based

aaloneftis

SSC-Addicted

Points: 443
More actions
December 19, 2011 at 8:31 am

#391160

Hi all
This is my first post. Thanks to all helpers.
I have a database table as below that is cumbersome to query, requiring a self join for each new column of the query.
The table structure is:
CREATE TABLE [X](
[CustomerID] [numeric](10, 0) NOT NULL,
[BlockID] [nvarchar](50) NOT NULL,
[IOID] [nvarchar](50) NOT NULL,
[IOType] [numeric](10, 0) NOT NULL,
[PropertyID] [nvarchar](50) NOT NULL,
[PropertyValue] [nvarchar](4000) NULL)
The first 5 fields form the primary key.
I am looking for help to produce a table with only CustomerID as the primary key and whose columns are the values of the
other 4 key fields.
Thanks for any help.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

salum SSCommitted Points: 1598 More actions · Answer 1

Try with using pivot feature in sql server.

http://msdn.microsoft.com/en-us/library/ms177410.aspx

java[/url]

Thomas LeBlanc SSCrazy Eights Points: 8085 More actions · Answer 2

I think you need to break the table into multiple tables with foreign keys. You should probably read Joe Celko's Stairway to Data and to Database Design and Gregory Larsen's Stairway to T-SQL DML. 3rd Normal Form is a good start for a beginner in database design.

Joe's

http://www.sqlservercentral.com/stairway/72899/

http://www.sqlservercentral.com/stairway/72400/

Greg's

http://www.sqlservercentral.com/stairway/75773/

Here is a start with CustomerHeader and CustomerIO, the same can be done for the Property information.

CREATE TABLE dbo.[CustomerHeader] (

[CustomerID] [numeric](10, 0) NOT NULL,

[CustName] [varchar](40) NOT NULL,

[Street] [varchar](40) NOT NULL,

[City] [varchar](40) NOT NULL,

[LotsOfOtherInfo] [char](250) NOT NULL,

CONSTRAINT [PK_CustomerHeader_CustomerID]

PRIMARY KEY CLUSTERED ( [CustomerID] ASC )

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,

ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY])

ON [PRIMARY]

CREATE TABLE dbo.[CustomerIO] (

[CustomerID] [numeric](10, 0) NOT NULL,

[IOID] [nvarchar](50) NOT NULL,

[IOType] [numeric](10, 0) NOT NULL,

[LotsOfOtherInfo] [char](250) NOT NULL,

CONSTRAINT [PK_CustomerIO_CustomerID_IOID]

PRIMARY KEY CLUSTERED ( [CustomerID] ASC, [IOID] ASC )

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,

ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY])

ON [PRIMARY]

ALTER TABLE dbo.[CustomerIO] WITH CHECK

ADD CONSTRAINT [FK_CustomerIO_CustomerHeader_CustomerID]

FOREIGN KEY([CustomerID])

REFERENCES dbo.[CustomerHeader] ([CustomerID])

GO

ALTER TABLE dbo.[CustomerIO] CHECK CONSTRAINT [FK_CustomerIO_CustomerHeader_CustomerID]

GO

Thomas LeBlanc, MVP Data Platform Consultant

aaloneftis SSC-Addicted Points: 443 More actions · Answer 3

Thanks for your answer. I have not done pivoting before (not even in Excel!) so I will experiment and see what comes out.

aaloneftis SSC-Addicted Points: 443 More actions · Answer 4

Thanks for the input. Also many thanks for the interesting links ... good reading coming up.

Obviously I need to work on this as I don't think it is straight forward otherwise else a query that I could use would be given.

Cadavre SSC-Forever Points: 41690 More actions · Answer 5

aaloneftis (12/21/2011)
Thanks for the input. Also many thanks for the interesting links ... good reading coming up.
Obviously I need to work on this as I don't think it is straight forward otherwise else a query that I could use would be given.

No, the reason that you haven't been given a query you can use is because your post lacks readily consumable sample data and expected result.

See this article for advise on how best to post questions and ensure you get an answer.[/url]

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

aaloneftis SSC-Addicted Points: 443 More actions · Answer 6

Many thanks for the quick reply and its content. I will get some sample data through hopefully by tomorrow.