June 5, 2013 at 7:33 am
I'm struggling with following issues that I would like to see if it can be resolved using SQL Queries
Following is the data (only sample data)
resourceIDName0 VariableValue0
16777220WKSRELEASE2
16777220WKSMAINT6 SCV20
16777220WKSOS W7_64
like to show the data as follow
resourceID internal version
16777220 W7_64 2.6 SCV20
Thx
June 5, 2013 at 7:51 am
Your explanation is not very clear
Can you explain the logic behind the expected results based on your sample data?
It would be a bit more helpful if you could add some DDL and convert your sample data into usable format as it will help us to give you tested results.
If you are not sure on how to do this, please go through the link in my signature.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 5, 2013 at 9:42 am
If I understood correctly, you need to pivot your data. A great way is to use CROSS TABS[/url]
With your data pivoted, you can concatenate the columns as you need them.
--I used a CTE to have the sample data, but you don't need it.
;WITH CTE( resourceID, Name0, VariableValue0) AS(
SELECT '16777220' resourceID, 'WKSRELEASE' Name0, '2' VariableValue0 UNION ALL
SELECT '16777220', 'WKSMAINT', '6 SCV20' UNION ALL
SELECT '16777220', 'WKSOS', 'W7_64'
)
SELECT resourceID,
MAX( CASE WHEN Name0 = 'WKSOS' THEN VariableValue0 END) + ' ' +
MAX( CASE WHEN Name0 = 'WKSRELEASE' THEN VariableValue0 END) + '.' +
MAX( CASE WHEN Name0 = 'WKSMAINT' THEN VariableValue0 END) AS [internal version]
FROM CTE
GROUP BY resourceID
June 5, 2013 at 9:54 am
Here is what I came up with. Please look at my setup as this is what you should provide instead having us guess at your table structure and data types.
We should be able to simply copy, paste, execute in SSMS to setup what we need to work your problem.
declare @TestTab table ( -- table for testing
ResourceId int, -- Don't know what the actual data type is but this should work for the small sample
Name0 varchar(32), -- Don't know what the actual data type is but this should work for the small sample
VariableValue0 varchar(32) -- Don't know what the actual data type is but this should work for the small sample
);
insert into @TestTab
values (16777220, 'WKSRELEASE', '2'),
(16777220, 'WKSMAINT', '6 SCV20'),
(16777220, 'WKSOS', 'W7_64'); -- Insert sample data
select * from @TestTab; -- verify data
with ResourceIds as (
select distinct
ResourceId
from
@TestTab
)
select
ri.ResourceId,
stuff((select '' + VariableValue0 + case Name0 when 'WKSOS' then ' ' when 'WKSRELEASE' then '.' else '' end
from @TestTab tt
where tt.ResourceId = ri.ResourceId
order by case Name0 when 'WKSOS' then 1 when 'WKSRELEASE' then 2 else 3 end
for xml path(''),TYPE).value('.','varchar(max)'),1,0,'') as InternalVersion
from
ResourceIds ri;
June 6, 2013 at 1:24 am
Perfect, that did the trick, thx very much for all the help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply