Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post Apr 03, 2012 01:02 PM by sivaram.yv_88
Mar 01, 2012 10:08 AM|LINK
I have one user table.
and second language table which contains language that user can speak
and third passport table which contains user's passport details(user can have multiple passport)
[I have 5 more table except these]
I want to convert these language and passport rows to columns into one result-set(having user details also) and in the result set it should show yes/no value for language and passport column for every user record depending on what language user can speak
and what passport they have.Output would be as given below:
We can do this by using PIVOT.I tried with single table but how to do it for multiple tables.
Mar 01, 2012 10:21 AM|LINK
First - Create a View with all join conditions .
second - Use Pivot on that View.
Mar 01, 2012 10:32 AM|LINK
i hope it will help you.
SELECT *FROM (SELECT month,id,income FROM all_business WHERE year="2011" and id IN(1,2,3)) PIVOT (SUM(income) FOR month IN(1 AS JUNE,2 AS JULY,3
AS AUGUST)) ORDER BY id
Mar 01, 2012 10:36 AM|LINK
This link display
Mar 01, 2012 11:02 AM|LINK
Check below example. modify as per your requirment
CREATE TABLE #TAB1 (userid int, username varchar(10), department varchar(10))
CREATE TABLE #TAB2 (uniqueid int, userid int, language varchar(20))
CREATE TABLE #TAB3 (uniqueid int, userid int, CountryPassport varchar(20))
INSERT INTO #TAB1
SELECT 1, 'aa', 'TT'
UNION ALL SELECT 2, 'bb', 'gg'
UNION ALL SELECT 3, 'cc', 'rr'
UNION ALL SELECT 4, 'dd', 'dd'
INSERT INTO #TAB2
UNION ALL SELECT 1,1,'Arabic'
UNION ALL SELECT 1,2,'Azerbaijani'
UNION ALL SELECT 1,3,'Bulgarian'
UNION ALL SELECT 1,4,'Chaochow'
UNION ALL SELECT 1,5,'Behdini'
INSERT INTO #TAB3
UNION ALL SELECT 1,1,'Malaysia'
UNION ALL SELECT 1,2,'Spain'
UNION ALL SELECT 1,3,'China'
UNION ALL SELECT 1,4,'India'
UNION ALL SELECT 1,5,'UK'
SELECT A.username, department, 'yes' as status, val into #temp
FROM #TAB1 A LEFT JOIN (
SELECT b.userid, b.username, language as val FROM #TAB2 a inner join #TAB1 b on a.userid = b.userid
SELECT b.userid, b.username, CountryPassport FROM #TAB3 a inner join #TAB1 b on a.userid = b.userid
) B ON A.userid = B.userid
DECLARE @DISTINCT_LIST VARCHAR(MAX), @QUERY NVARCHAR(MAX), @COL_LIST VARCHAR(MAX)
SELECT @DISTINCT_LIST = STUFF((SELECT ',[' + val + ']' FROM #temp FOR XML PATH('')),1,1, '')
SELECT @COL_LIST = STUFF((SELECT ',ISNULL([' + val + '], ''no'') as ' + val FROM #temp FOR XML PATH('')),1,1, '')
SET @QUERY = 'SELECT username, department, ' + @COL_LIST + ' FROM #temp
PIVOT(MAX(status) FOR val IN (' + @DISTINCT_LIST + ')) PVT ORDER BY username'
EXEC sp_executesql @QUERY
DROP TABLE #TAB1
DROP TABLE #TAB2
DROP TABLE #TAB3
DROP TABLE #temp
Mar 01, 2012 03:56 PM|LINK
you can go with this now
Mar 02, 2012 05:00 AM|LINK
thanks sandeep.thanks a lot
Its exactly what i needed
Mar 02, 2012 08:39 AM|LINK
I need more suggestion regarding previous result set.
Moving further with other tables
I have a table tblBCE and data in the given below:
and a master table mstBCE which contains label text to display in front end application. This table contains data for other tab also but currently i want only for tabType 'BCE',I will do for other tab myself once i got the concept.
There is no relation between these two tables.In front end application i display these data only by sequence.i means in front end it display as given below for one user:
so the output would be. These column will display after language and countrypassport column list.
Mar 02, 2012 10:12 AM|LINK
You are welcome.
There should be BCEid on UserId in mstBCE Table, otherwise you would not be able to link the data
Mar 02, 2012 10:24 AM|LINK
So can we do something like by hard code the Option1 to "tab display text111111 BCE",Option2 to "tab display text222222 BCE" etc.
and there are only three column for BCE type which is fixed.