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: Tuesday, August 11, 2015 7:38 AM
Points: 164, Visits: 719
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
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 1,551, Visits: 604
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
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 1,551, Visits: 604
glad i could help
Post #1398373
Posted Wednesday, December 19, 2012 8:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:17 PM
Points: 14,584, Visits: 14,357
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 Thursday, December 20, 2012 8:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:17 PM
Points: 14,584, Visits: 14,357
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