results to columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

results to columns

Author

Topic

HalaszJ
Yak Posting Veteran

59 Posts

Posted - 2009-12-30 : 05:56:20

I have a database full of people that speak other languages, there is a lookup table that has the username and the language id of the language they speak. Some speak many.

My currect query I have a function that concats the languages together, like this..

User | EN / ES / IT

What I want is to return those values to multiple columns like

User | EN | ES | IT

so that it can be exported to excel and sorted and filtered. There are dozens more columns than just those 2, but is there a possible way to achieve my goal?

Sachin.Nand

2937 Posts

Posted - 2009-12-30 : 06:31:44

You need dynamic pivot.Have a look at this link

http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx

PBUH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-30 : 07:38:20

or
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-12-30 : 07:47:06

[code]
DROP TABLE #temp
DECLARE @str TABLE(userid INT,DATA VARCHAR(128))
INSERT INTO @str SELECT 1,'EN/IT/FR/ES/NA'

INSERT INTO @str SELECT 2,'EN/IT'

INSERT INTO @str SELECT 3,'EN/IT /NA'

SELECT userid,
REPLACE(SUBSTRING(DATA,CHARINDEX('/',DATA,v.number),ABS(CHARINDEX('/',DATA,CHARINDEX('/',DATA,v.number)+1)-CHARINDEX('/',DATA,v.number))),'/','')AS VALUE
INTO #temp
FROM @str AS s
INNER JOIN master..spt_values AS v ON v.Type = 'P'
AND v.number > 0
AND v.number <= LEN(s.data)
WHERE SUBSTRING('/' + s.data, v.number, 1) = '/'

DECLARE @Sql VARCHAR(MAX)
DECLARE @Cols VARCHAR(MAX)

SELECT @cols = ISNULL(@Cols,'')+',['+ VALUE+']'
FROM (SELECT DISTINCT VALUE FROM #temp)s

SELECT @Sql =
'SELECT userid'+@cols+'
FROM #temp
PIVOT (MAX(value) FOR value IN ('+STUFF(@cols,1,1,'')+'))p'

PRINT @Sql

EXEC(@Sql)
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources