Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Changing the table structure Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, December 18, 2012 11:11 PM
 SSC Rookie Group: General Forum Members Last Login: Monday, July 15, 2013 4:16 AM Points: 38, Visits: 250
 Hi,The below table is my sourceSkill BU1 BU2 BU3Skill1 0 0 0Skill2 2 6 0Skill3 0 0 7Skill4 4 0 0Skill5 0 7 8I would like to convert it into the below tableBU Skill ValueBU1 Skill1 0BU1 Skill2 2BU1 Skill3 0BU1 Skill4 4BU1 Skill5 0BU2 Skill1 0BU2 Skill2 6BU2 Skill3 0BU2 Skill4 0BU2 Skill5 7BU3 Skill1 0BU3 Skill2 0BU3 Skill3 7BU3 Skill4 0BU3 Skill5 8How can this be done using SSIS and/or T-SQL?Please help.
Post #1398179
 Posted Wednesday, December 19, 2012 12:56 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, November 16, 2016 5:33 AM Points: 169, Visits: 773
 You should probably use pivot operation (I hope somebody will post an example), but this should also work:`use tempdbdeclare @t table ( Skill varchar(10) not null, BU1 int not null, BU2 int not null, Bu3 int not null)insert into @tvalues ('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 temporder by BU, Skill`
Post #1398219
 Posted Wednesday, December 19, 2012 1:31 AM
 SSC Rookie Group: General Forum Members Last Login: Monday, July 15, 2013 4:16 AM Points: 38, Visits: 250
 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.
Post #1398227
 Posted Wednesday, December 19, 2012 2:01 AM
 SSCommitted Group: General Forum Members Last Login: Thursday, September 1, 2016 12:16 AM Points: 1,828, Visits: 508
 i am sure i have seen a script on SSC for a dynamic pivot function; think this is the one : CLR Pivot
Post #1398238
 Posted Wednesday, December 19, 2012 2:45 AM
 SSCrazy Group: General Forum Members Last Login: Wednesday, November 2, 2016 3:44 AM Points: 2,016, Visits: 994
 Hi, This should work for you.IF EXISTS (Select Name FROM Utility.sys.objects Where name ='tfnStringParser')BEGIN DROP FUNCTION dbo.tfnStringParserENDGOCREATE FUNCTION [dbo].[tfnStringParser]( @inputString Varchar(8000), @Delimiter CHAR(1))RETURNS @parsedValues TABLE (ParsedColumn VARCHAR(200))ASBEGIN 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 RETURNENDGODECLARE @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
Post #1398251
 Posted Wednesday, December 19, 2012 4:19 AM
 SSC Rookie Group: General Forum Members Last Login: Monday, July 15, 2013 4:16 AM Points: 38, Visits: 250
 Thanks a lot Simon!!Your code worked perfectly!!
Post #1398290
 Posted Wednesday, December 19, 2012 6:53 AM
 SSCrazy Group: General Forum Members Last Login: Wednesday, November 2, 2016 3:44 AM Points: 2,016, Visits: 994
Post #1398373
 Posted Wednesday, December 19, 2012 8:28 AM
 SSCoach Group: General Forum Members Last Login: Today @ 2:44 PM Points: 16,134, Visits: 16,835
 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 PivotOr 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 Moden's splitter.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2)
Post #1398448
 Posted Thursday, December 20, 2012 8:16 AM
 SSCoach Group: General Forum Members Last Login: Today @ 2:44 PM Points: 16,134, Visits: 16,835
 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 Moden's splitter.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2)
Post #1398996

 Permissions