December 18, 2012 at 11:11 pm
Hi,
The below table is my source
SkillBU1BU2BU3
Skill1000
Skill2260
Skill3007
Skill4400
Skill5078
I would like to convert it into the below table
BUSkillValue
BU1Skill10
BU1Skill22
BU1Skill30
BU1Skill44
BU1Skill50
BU2Skill10
BU2Skill26
BU2Skill30
BU2Skill40
BU2Skill57
BU3Skill10
BU3Skill20
BU3Skill37
BU3Skill40
BU3Skill58
How can this be done using SSIS and/or T-SQL?
Please help.
December 19, 2012 at 12:56 am
You should probably use pivot operation (I hope somebody will post an example), but this should also work:
use tempdb
declare @t table (
Skill varchar(10) not null,
BU1 int not null,
BU2 int not null,
Bu3 int not null
)
insert into @t
values
('Skill1', 0, 0, 0),
('Skill2', 2, 6, 0),
('Skill3', 0, 0, 7),
('Skill4', 4, 0, 0),
('Skill5', 0, 7, 8)
;with temp as (
select 'BU1' BU, Skill, BU1 Value from @t union all
select 'BU2' BU, Skill, BU2 Value from @t union all
select 'BU3' BU, Skill, BU3 Value from @t
)
select *
from temp
order by BU, Skill
December 19, 2012 at 1:31 am
Hi Simon,
Thanks for ur input.
But, the no. and name of columns BU1, BU2 etc... and the rows Skill1, Skill2 etc... is also fixed.
I cannot hardcode any values.Hence I cannot use pivot.
Kindly help when all the column names and rows are dynamic.
December 19, 2012 at 2:01 am
December 19, 2012 at 2:45 am
Hi, This should work for you.
IF EXISTS (Select Name FROM Utility.sys.objects Where name ='tfnStringParser')
BEGIN
DROP FUNCTION dbo.tfnStringParser
END
GO
CREATE FUNCTION [dbo].[tfnStringParser]
(
@inputString Varchar(8000),
@Delimiter CHAR(1)
)
RETURNS
@parsedValues TABLE (ParsedColumn VARCHAR(200))
AS
BEGIN
DECLARE @spos INT
DECLARE @epos INT
IF RIGHT(@inputString,1)<> @Delimiter
SET @inputString= @InputString + @Delimiter
SET @spos =1
WHILE CHARINDEX(@delimiter,@InputString,@spos) <> 0
BEGIN
SET @epos=CHARINDEX(@delimiter,@inputString,@spos)
Insert into @parsedValues
SELECT SUBSTRING(@InputString,@spos,@epos - @spos)
SET @spos =@epos +1
END
RETURN
END
GO
DECLARE @Columns NVARCHAR(4000)
DECLARE @SQLstr NVARCHAR(MAX)
SET @Columns =''
SET @SQLstr = ''
--Just using your example, this table could be any size.
CREATE TABLE skill(Skill VARCHAR(15),BU1 INT,BU2 INT , BU3 INT)
INSERT INTO skill (Skill,BU1,BU2,BU3)
VALUES
('Skill1', 0, 0, 0),
('Skill2', 2, 6, 0),
('Skill3', 0, 0, 7),
('Skill4', 4, 0, 0),
('Skill5', 0, 7, 8)
SELECT @Columns = @Columns + COLUMN_NAME +','
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME ='SKILL' and COLUMN_NAME Like 'BU%'
SELECT @Columns= LEFT(@Columns,LEN(@Columns)-1)
SELECT @SQLstr = @SQLstr + 'SELECT ''' +[ParsedColumn] +''',SKILL,' + [ParsedColumn] + ' as [Value] FROM Skill UNION ALL' +CHAR(13)
FROM [Utility].[dbo].[tfnStringParser] (@Columns,',')
SELECT @SQLStr =LEFT(@SQLStr,LEN(@SQLStr)- (LEN('UNION ALL')+1))
EXEC(@SQLSTR)
DROP TABLE skill
December 19, 2012 at 4:19 am
Thanks a lot Simon!!
Your code worked perfectly!! 🙂
December 19, 2012 at 6:53 am
glad i could help
December 19, 2012 at 8:28 am
Terry300577 (12/19/2012)
i am sure i have seen a script on SSC for a dynamic pivot function; think this is the one : CLR Pivot[/url]
Or if you want a completely t-sql dynamic pivot take a look at the links in my signature about cross tabs.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 20, 2012 at 8:16 am
Solitary Reaper (12/19/2012)
Thanks a lot Simon!!Your code worked perfectly!! 🙂
I would recommend to both you and Simon that you take a look at the link in my signature about splitting strings. The while loop method is very slow in comparison to the function you will find when reading that article.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply