convert Row to column.

Question

Post reply

convert Row to column.

dpalani

SSC Journeyman

Points: 83
More actions
January 18, 2011 at 2:05 am

#232318

Hi,
How to convert row to column for the below eg. I used pivot to do this. But i am getting only one account for an ACMnumber.
But i need 3 records for an account with group by dataset as per below eg. i Have given a eg of one ACMnumber, but i have millions of ACMnumber in that table .
Can any one give me a query to do this.
Also given the eg query which i tried.
Table: vacmvariables
ACMnumberDatasetStringvalue
153588custAcctNo6U7000000
153588custAcctNo6Y0000001
153588custAcctNo6Y0000002
153588custID443
153588accountTitleIRA FBO Loelei C Dehart DecD
153588accountTitleMartha Johnson
153588accountTitleWilliam C Kearns & Joan
153588shortNameW00001
153588shortNameP00009
153588shortNameP00003
Desired OUTPUT
custAcctNocustIDaccountTitle shortName
6U7000000443IRA FBO Loelei C Dehart DecD W00001
6Y0000001Martha Johnson P00009
6Y0000002William C Kearns & Joan P00003
Query i tried
select custAcctNo,custID,shortName,accountTitle
from
(select Acmnumber, dataset, stringvalue from vacmvariables
where acmnumber = 153588)
up pivot ( max (stringvalue ) for dataset in ( custAcctNo,custID,shortName,accountTitle)) as pvt
order by acmnumber
Need smillar kind of query to return 3 rows as desired above.

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

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

kupy SSCommitted Points: 1652 More actions · Answer 1

hi,

How do you know that "6U7000000 443 IRA FBO Loelei C Dehart DecD W00001" is 1 row and not any other combination?

Try to add row_number to the query, the row number will give you another group by field.

something like this

select custAcctNo,custID,shortName,accountTitle

from

(select Acmnumber, dataset, stringvalue, row_number() over (partition by Acmnumber, dataset order by FIELD) as rnum from vacmvariables

where acmnumber = 153588)

up pivot ( max (stringvalue ) for dataset in ( custAcctNo,custID,shortName,accountTitle)) as pvt

order by acmnumber

dpalani SSC Journeyman Points: 83 More actions · Answer 2

Thanks,

This is what i need.

How does rownum aggregates from 10 rows to 3 rows. can you explain.

kupy SSCommitted Points: 1652 More actions · Answer 3

Hi,

I'm happy that I can help.

The row number help with cause we have a new column to group by. If you'll add the rnum to the outer select you'll see how he effects the query.

TRACIEJOHNSON30 Grasshopper Points: 11 More actions · Answer 4

I need some assistance....I have the screen shot attached below. I would like the output to have column headers 'vessel' and each of the twelve months. I would like to have a 'po number' count for every month for each 'vessel'

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

TRACIEJOHNSON30 (10/9/2013)
I need some assistance....I have the screen shot attached below. I would like the output to have column headers 'vessel' and each of the twelve months. I would like to have a 'po number' count for every month for each 'vessel'

Hi and welcome to the forums. It is best if you start your own thread instead of hijacking a 2 year old thread. Also, this is a sql server site, not a MySql site, so you will find that the syntax we come up with will work for sql server. It may not work for MySql. Last but not least, on any technical forum where you post for help you need to provide enough information for people to be able to help. A screen shot of a query does not offer much in the way of detail. You should instead include:

1. Sample DDL in the form of CREATE TABLE statements

2. Sample data in the form of INSERT INTO statements

3. Expected results based on the sample data

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/