June 7, 2012 at 1:12 am
Hi Guys!!!
How to select Row Value into column Value ??
This the Sample Table
IdsNameValueParentId
100Define KIA2
200Measure Tollgate2
300ControlWorkshield2
400AnalysefileFolder2
500ImproveGAte2
I want the Result Like this
ParentIdPhase1 Phase2Phase3phase4phase5ParentId
100DefineMeasureControlAnalyseImprove2
June 7, 2012 at 2:05 am
It's a matter of pivoting your data (see PIVOT for more detail).
Try something like this:
-- Sample data
DECLARE @sampleData TABLE (
Idsint,
Name nvarchar(50),
Value nvarchar(50),
ParentId int
)
INSERT INTO @sampleData VALUES
(100, 'Define', 'KIA', 2),
(200, 'Measure', 'Tollgate', 2),
(300, 'Control', 'Workshield', 2),
(400, 'Analyse', 'fileFolder', 2),
(500, 'Improve', 'GAte', 2)
--Solution
SELECT
MinIds
, Phase1
, Phase2
, Phase3
, Phase4
, Phase5
, ParentId
FROM (
SELECT
MIN(Ids) OVER (PARTITION BY ParentId) AS MinIds
, Name
, ParentId
, 'Phase'
+ CAST(
ROW_NUMBER() OVER (PARTITION BY ParentId ORDER BY Ids)
AS varchar(10)
)
AS phaseId
FROM @sampleData
) AS src
PIVOT ( MIN(Name)
FOR phaseId IN (
[Phase1],
[Phase2],
[Phase3],
[Phase4],
[Phase5]
)
) AS pvt
You have two ParentId columns in your expected results. I don't understand what the first represents, so I filled it with the minimum Ids.
Hope this helps
Gianluca
-- Gianluca Sartori
June 7, 2012 at 2:39 am
Hi
This code also returns the result you want:
create table SampleTable(
id int,
[name] varchar(20),
[Value] varchar(20),
parentId int
)
insert into SampleTable(id,name,Value,parentId)
values
(100,'Define','KIA',2),
(200,'Measure','Tollgate',2),
(300,'Control','Workshield',2),
(400,'Analyse','fileFolder',2),
(500,'Imporve','Gate',2)
go
declare @id int
declare @name varchar(20)
declare @value varchar(20)
declare @parentId int
declare @msg varchar(1000)
declare Kursor Cursor for
with MyCte (id,name,value,parentId)
as
(
select id,name,value,parentId from SampleTable where parentId = 2
)
select id,name,value,parentId from MyCte
open Kursor
fetch next from Kursor
into @id,@name,@value,@parentId
set @msg = @id
while @@fetch_status = 0
begin
set @msg = @msg +' '+@name
fetch next from Kursor
into @id,@name,@value,@parentId
end
select @msg 'rows columns in a row'
close Kursor
deallocate Kursor
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
June 7, 2012 at 2:52 am
IgorMi (6/7/2012)
HiThis code also returns the result you want:
This sure does the trick, but please take into account that avoiding cursors is considered a best practice.
Generally speaking, cursors are slow compared to set-based statements, consume a lot of memory and lead to unwanted locking.
Whenever possible, I suggest avoiding the use of cursors.
-- Gianluca Sartori
June 7, 2012 at 3:05 am
Gianluca Sartori (6/7/2012)
IgorMi (6/7/2012)
HiThis code also returns the result you want:
This sure does the trick, but please take into account that avoiding cursors is considered a best practice.
Generally speaking, cursors are slow compared to set-based statements, consume a lot of memory and lead to unwanted locking.
Whenever possible, I suggest avoiding the use of cursors.
Yes I agree,
The code I provided above may be customized i.e. embeded in a stored procedure and if you additionally set a filter, for example "where parentId = 2", then the set becomes small and you cannot feel the difference between set-based statements and cursors.
Yes, ... and as i know, cursors are OK if you have up to 1000 rows. I think this primer is such nature that you don't have 100s of Phases 🙂 (see above), so I think cursors are ok here.
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
June 7, 2012 at 3:15 am
IgorMi (6/7/2012)
I think cursors are ok here.
Sorry to disagree.
-- Gianluca Sartori
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply