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
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
SQLXMLMicrosoft SQL Server
Last Comment
Terry Ambrosius
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.
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks all for the info.
If code is variable (not known upfront or a large number) you might need to do this with a script