December 18, 2012 at 11:11 pm
Hi,
The below table is my source
Skill BU1 BU2 BU3
Skill1 0 0 0
Skill2 2 6 0
Skill3 0 0 7
Skill4 4 0 0
Skill5 0 7 8
I would like to convert it into the below table
BU Skill Value
BU1 Skill1 0
BU1 Skill2 2
BU1 Skill3 0
BU1 Skill4 4
BU1 Skill5 0
BU2 Skill1 0
BU2 Skill2 6
BU2 Skill3 0
BU2 Skill4 0
BU2 Skill5 7
BU3 Skill1 0
BU3 Skill2 0
BU3 Skill3 7
BU3 Skill4 0
BU3 Skill5 8
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