October 12, 2010 at 1:25 pm
Below is the aproach i have tried. Please suggest a better one...
------====================================================================
ALTER
FUNCTION [fn_gettable]()
RETURNS
@ci_table TABLE (
p_id
int,
c1
nvarchar(128),
c2
nvarchar(128),
c3
nvarchar(128),
c4
nvarchar(128),
c5
nvarchar(128),
c6
nvarchar(128),
c7
nvarchar(128),
c8
nvarchar(128),
c9
nvarchar(128),
c10
nvarchar(128)
)
AS
BEGIN
--------============
DECLARE @temp_ci Table
(
ci_id
int,
id
int,
ci
nvarchar(128)
)
INSERT INTO @temp_ci
SELECT linkID, nID, rest
FROM ink
WHERE rest != ''
ORDER BY anID
DECLARE @temp TABLE
(
id
Int IDENTITY, p_id int, c1 nvarchar(128),
c2
nvarchar(128), c3 nvarchar(128), c4 nvarchar(128),
c5
nvarchar(128), c6 nvarchar(128), c7 nvarchar(128),
c8
nvarchar(128), c9 nvarchar(128), c10 nvarchar(128)
)
BEGIN
----============
DECLARE @linkID INT, @anId INT, @rest nvarchar(128)
DECLARE @current_id INT, @count INT
----============
SET @current_id = 0
SET @count = 0
WHILE EXISTS (SELECT * FROM @temp_ci)
BEGIN
SELECT TOP 1
@linkID
= ci_id,
@anId
= id,
@rest
= ci
FROM @temp_ci
IF @current_id = 0
BEGIN
SET @current_id = @anId
INSERT INTO @temp
(p_id, c1)
VALUES
(@anId, @rest)
SET @count = @count+1
END
ELSE IF @current_id = @anId AND @count = 1
BEGIN
SET @current_id = @anId
UPDATE @temp
SET c2 = @rest
WHERE p_id = @anId
SET @count = @count+1
END
ELSE IF @current_id = @anId AND @count = 2
BEGIN
SET @current_id = @anId
UPDATE @temp
SET c3 = @rest
WHERE p_id = @anId
SET @count = @count+1
END
ELSE IF @current_id = @anId AND @count = 3
BEGIN
SET @current_id = @anId
UPDATE @temp
SET c4 = @rest
WHERE p_id = @anId
SET @count = @count+1
END
ELSE IF @current_id = @anId AND @count = 4
BEGIN
SET @current_id = @anId
UPDATE @temp
SET c5 = @rest
WHERE p_id = @anId
SET @count = @count+1
END
ELSE IF @current_id = @anId AND @count = 5
BEGIN
SET @current_id = @anId
UPDATE @temp
SET c6 = @rest
WHERE p_id = @anId
SET @count = @count+1
END
ELSE IF @current_id = @anId AND @count = 6
BEGIN
SET @current_id = @anId
UPDATE @temp
SET c7 = @rest
WHERE p_id = @anId
SET @count = @count+1
END
ELSE IF @current_id = @anId AND @count = 7
BEGIN
SET @current_id = @anId
UPDATE @temp
SET c8 = @rest
WHERE p_id = @anId
SET @count = @count+1
END
ELSE IF @current_id = @anId AND @count = 8
BEGIN
SET @current_id = @anId
UPDATE @temp
SET c9 = @rest
WHERE p_id = @anId
SET @count = @count+1
END
ELSE IF @current_id = @anId AND @count = 9
BEGIN
SET @current_id = @anId
UPDATE @temp
SET c10 = @rest
WHERE p_id = @anId
SET @count = @count+1
END
ELSE
BEGIN
SET @current_id = 0
SET @count = 0
INSERT INTO @temp
(p_id, c1)
VALUES
(@anId, @rest)
SET @count = @count+1
SET @current_id = @anId
END
DELETE FROM @temp_ci
WHERE ci_id = @linkID
END
INSERT INTO @cI_table ( p_id, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10)
SELECT p_id, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10
FROM @temp
RETURN
END
END
------====================================================================
------====================================================================
October 12, 2010 at 1:32 pm
Its really hard to tell the final product you are after given the information you provided, but you may be interested in reading up on the PIVOT syntax -
http://msdn.microsoft.com/en-us/library/ms177410.aspx
If you are after a more detailed answer, please provide more details about what you are after.
October 12, 2010 at 1:41 pm
Please take a look at the two articles in my signature: Crosstab and Pivot tables, Part 1 and Part 2. It looks like these will handle what you are after... Part 2 even goes into doing it dynamically, which your procedure may benefit from.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 13, 2010 at 11:06 am
Thanks a lot. These articles are really helpful.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply