Link to home
Create AccountLog in
Avatar of Terry Ambrosius
Terry Ambrosius

asked on

In sql, how to roll up multiple rows to only one row.

Hello,   did not think this would be to difficult, but cannot find a clean way to do this.     First I am not a SQL person by no means.
I have a table with multiple rows of data and need to roll it up to one row of data.  

Example:
spec       code
12345    abc
12345    def
67891    xyz
67891    rst

need the data to look like
spec      code1 code2
12345    abc     def
67891    xyz      rst

I tried the xml feature, but how to get rid of the xml tags in the output,  
Tried stuff command, but code not get it to work yet.  

Must be an easier way.     Any help/example would be appreciated.
Would be creating a view once I figure out how to make that work.
Thanks
Avatar of Julian Hansen
Julian Hansen
Flag of South Africa image

You are wanting to do a PIVOT. Is the number of variations on code a small or a large number?

If code is variable (not known upfront or a large number) you might need to do this with a script
Here is a reference to pivot example, https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

As Julian pointed out, your table structure requires additional work.
Commonly,
Id,type,value
12345,code1,abc
12345,code2,def

That then gets pivoted to the table you displayed.
The type is the column on which the data is pivoted.

In a structure such as yours, you may have to as Julian pointed out run a query that converts your two column structure to three adding type.
On which the pivot can act.
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Terry Ambrosius
Terry Ambrosius

ASKER

Thanks all for the info.