Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Changing the table structure Expand / Collapse
Author
Message
Posted Tuesday, December 18, 2012 11:11 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 15, 2013 4:16 AM
Points: 38, Visits: 250
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.
Post #1398179
Posted Wednesday, December 19, 2012 12:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 5, 2014 1:33 AM
Points: 158, Visits: 676
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


Post #1398219
Posted Wednesday, December 19, 2012 1:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 8:05 AM
Points: 1,729, Visits: 491
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!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, December 11, 2014 3:09 AM
Points: 991, Visits: 463
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
Post #1398251
Posted Wednesday, December 19, 2012 4:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, December 11, 2014 3:09 AM
Points: 991, Visits: 463
glad i could help
Post #1398373
Posted Wednesday, December 19, 2012 8:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, December 19, 2014 2:05 PM
Points: 13,327, Visits: 12,820
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


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 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

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, December 19, 2014 12:12 PM
Points: 1,945, Visits: 3,180
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 Skills
USING Foobar
ON Skills.skill_name = Foobar.skill_name
WHEN MATCHED
THEN 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 Personnel
WHERE emp = 'guy who did this to you!';

Untested.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1398791
Posted Thursday, December 20, 2012 8:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, December 19, 2014 2:05 PM
Points: 13,327, Visits: 12,820
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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse