InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 08:27:14 GMT20http://www.sqlservercentral.com/Forums/Topic1398179-392-1.aspx[quote][b]Solitary Reaper (12/19/2012)[/b][hr]Thanks a lot Simon!!Your code worked perfectly!! :-)[/quote]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.Thu, 20 Dec 2012 08:16:53 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1398179-392-1.aspxPlease 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.Wed, 19 Dec 2012 23:37:08 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1398179-392-1.aspx[quote][b]Terry300577 (12/19/2012)[/b][hr]i am sure i have seen a script on SSC for a dynamic pivot function; think this is the one : [url=http://www.sqlservercentral.com/articles/.Net/94922/]CLR Pivot[/url][/quote]Or if you want a completely t-sql dynamic pivot take a look at the links in my signature about cross tabs.Wed, 19 Dec 2012 08:28:45 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1398179-392-1.aspxglad i could helpWed, 19 Dec 2012 06:53:14 GMTSimonHhttp://www.sqlservercentral.com/Forums/Topic1398179-392-1.aspxThanks a lot Simon!!Your code worked perfectly!! :-)Wed, 19 Dec 2012 04:19:32 GMTSolitary Reaperhttp://www.sqlservercentral.com/Forums/Topic1398179-392-1.aspxHi, 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 skillWed, 19 Dec 2012 02:45:19 GMTSimonHhttp://www.sqlservercentral.com/Forums/Topic1398179-392-1.aspxi am sure i have seen a script on SSC for a dynamic pivot function; think this is the one : [url=http://www.sqlservercentral.com/articles/.Net/94922/]CLR Pivot[/url]Wed, 19 Dec 2012 02:01:06 GMTTerry300577http://www.sqlservercentral.com/Forums/Topic1398179-392-1.aspxHi 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.Wed, 19 Dec 2012 01:31:58 GMTSolitary Reaperhttp://www.sqlservercentral.com/Forums/Topic1398179-392-1.aspxYou should probably use pivot operation (I hope somebody will post an example), but this should also work:[code]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[/code]Wed, 19 Dec 2012 00:56:47 GMT_simon_http://www.sqlservercentral.com/Forums/Topic1398179-392-1.aspxHi,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.Tue, 18 Dec 2012 23:11:12 GMTSolitary Reaper