Convert multiple rows into columns

Question

Post reply

Convert multiple rows into columns

vishal.chawla

SSC Rookie

Points: 37
More actions
April 6, 2009 at 10:17 am

#132898

Hi All,
I need help with writing this query on SLQ server 2005. Basically, my data in the "type" field (carrier_insurance.type) has multiple values i.e. auto liability, worker's compensation, general liability, etc.
I want to split the values and represent it in different columns as one carrier can have multiple insurance types.
Below is my query so far:
SELECT Carrier.[Key], Carrier.CreateDate, Carrier.Name, Carrier.Uid, Carrier.City, Carrier.State, Carrier.Zip, Carrier.IsW9Verify, Carrier.IsContractOnFile,
Carrier.ComplianceStatus, Carrier.InsuranceValue, Carrier.CarrierOfferValue, Carrier.CoverageValue, Carrier_Insurance.[Key] AS Expr1,
Carrier_Insurance.Type, Carrier_Insurance.CarrierKey, Carrier_Insurance.Limit, Carrier_Insurance.CarrierName, Carrier_Insurance.ExpireDate,
Carrier_Insurance.VerifyDate, Carrier_Insurance.Date, Carrier_Insurance.IsContractOnFile AS Expr2, Carrier_Insurance.ContractDate,
Carrier_Insurance.Note, Carrier_Insurance.BrokerName
FROM Carrier INNER JOIN
Carrier_Insurance ON Carrier.[Key] = Carrier_Insurance.CarrierKey
I would appreciate any help.
Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 1

Hi

Could you please provide table structures.

If you want to convert rows into columns then you can try out PIVOT.

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 2

Confirm Vijaya Kadiyala. DDL and some sample data would be helpful to help. 😉

Please have a look to the link in my signature.

Greets

Flo

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 3

Hi

Sample data is very much required like "what is the input" and "what is the out put" you are looking for. 🙂

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

vishal.chawla SSC Rookie Points: 37 More actions · Answer 4

Hi All,

Attached is sample data in excel file. I want to display the colored data in column K,L & M as a single row. Please help me.

Thanks,

Vishal

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 5

vishal.chawla (4/6/2009)
Hi All,
Attached is sample data in excel file. I want to display the colored data in column K,L & M as a single row. Please help me.
Thanks,
Vishal

I, for one, don't have Excel 2007 so that's pretty useless. Also, you really need to read the link that Flo directed you to because some of us won't even touch an Excel spreadsheet for data.

The reason why folks ask for the particular format identified in the link that Flo directed you to is so they can actually test their solution using your data to make sure you have the best answer possible... without having to go through the pain of converting a spreadsheet. Not trying to give you a hard time... trying to get you a better answer.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 6

Actually, I got a blank page on Flo's link... a working link exists in my signature line below. Do yourself a favor and read the article attached to it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vishal.chawla SSC Rookie Points: 37 More actions · Answer 7

Am not a sql expert and I used the excel 07 file as an attachment for sample data as it was one of the file type options. Am reattaching the sample data in xls. format.

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 8

vishal.chawla (4/7/2009)
Am not a sql expert and I used the excel 07 file as an attachment for sample data as it was one of the file type options. Am reattaching the sample data in xls. format.

It's been a couple of days and, as you can tell, not too many folks are biting on your problem. Answers would probably come quicker and better if you did like I suggested. Read the link in my signature below and post the table definition and the readily consumable data that way and you'll have much better luck. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)