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: Thursday, October 24, 2013 6:02 AM Points: 156, Visits: 659
 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: Monday, August 19, 2013 8:40 AM Points: 1,716, Visits: 474
 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
 SSC Eights! Group: General Forum Members Last Login: Tuesday, November 26, 2013 8:34 AM Points: 934, Visits: 413
 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
 SSC Eights! Group: General Forum Members Last Login: Tuesday, November 26, 2013 8:34 AM Points: 934, Visits: 413
Post #1398373
 Posted Wednesday, December 19, 2012 8:28 AM
 SSChampion Group: General Forum Members Last Login: Today @ 3:16 PM Points: 10,876, Visits: 10,037
 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 Wednesday, December 19, 2012 11:37 PM
 SSCommitted Group: General Forum Members Last Login: Tuesday, January 15, 2013 11:11 AM Points: 1,945, Visits: 2,782
 Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. This is minimal polite behavior on SQL forums. What you seem to want, based on no specs or DDL, is to normalize this non-table.CREATE TABLE Foobar(skill_name NOT NULL PRIMARY KEY, --- repeated group! bu_1 SMALLINT NOT NULL CHECK (bu_1 >= 0), bu_2 SMALLINT NOT NULL CHECK (bu_2 >= 0), bu_3 SMALLINT NOT NULL CHECK (bu_3 >= 0));Now get the target table started: CREATE TABLE Skills(skill_name NOT NULL, bu_seq SMALLINT NOT NULL CHECK(bu_nbr IN (1, 2, 3, 4, 5)) PRIMARY KEY (skill_name, bu_nbr), bu_score SMALLINT DEFAULT 0 NOT NULL CHECK (bu_score >= 0));--load empty rows INSERT INTO Skills (skill_name, bu_seq)SELECT F.skill_name, S.seq FROM Foobar AS F CROSS JOIN SELECT S.seq FROM (VALUES (1), (2), (3), (4), (5)) AS S(seq);-- move foobar to new normalized table. MERGE INTO SkillsUSING FoobarON Skills.skill_name = Foobar.skill_nameWHEN MATCHEDTHEN UPDATE SET Skills.bu_score = CASE WHEN Skills.bu_seq = 1 THEN Foobar.bu_1 WHEN Skills.bu_seq = 2 THEN Foobar.bu_2 WHEN Skills.bu_seq = 3 THEN Foobar.bu_3 ELSE 0 END;--solve real real problem:DELETE FROM PersonnelWHERE emp = 'guy who did this to you!';Untested. Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQL Data and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL
Post #1398791
 Posted Thursday, December 20, 2012 8:16 AM
 SSChampion Group: General Forum Members Last Login: Today @ 3:16 PM Points: 10,876, Visits: 10,037
 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