and second language table which contains language that user can speak
bla-bla-bla
and third passport table which contains user's passport details(user can have multiple passport)
bla-bla-bla
[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:
bla-bla-bla
We can do this by using PIVOT.I tried with single table but how to do it for multiple tables.
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
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
SELECT 1,1,'American'
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
SELECT 1,1,'Honkong'
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
union all
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
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
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.
Sukhjeevan S...
Member
38 Points
51 Posts
How to convert Rows from multiple tables into Columns with values in Sql Server 2005
Mar 01, 2012 10:08 AM|LINK
Hi,
I have one user table.
bla-bla-bla
and second language table which contains language that user can speak
bla-bla-bla
and third passport table which contains user's passport details(user can have multiple passport)
bla-bla-bla
[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:
bla-bla-bla
We can do this by using PIVOT.I tried with single table but how to do it for multiple tables.
Thanks
shwetamber
Participant
1252 Points
294 Posts
Re: How to convert Rows from multiple tables into Columns with values in Sql Server 2005
Mar 01, 2012 10:21 AM|LINK
Hi,
First - Create a View with all join conditions .
second - Use Pivot on that View.
Regards
Shwetamber
bhaskar.mule
Contributor
2270 Points
659 Posts
Re: How to convert Rows from multiple tables into Columns with values in Sql Server 2005
Mar 01, 2012 10:32 AM|LINK
hi friend,
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
Site:Rare technical solutions
Sukhjeevan S...
Member
38 Points
51 Posts
Re: How to convert Rows from multiple tables into Columns with values in Sql Server 2005
Mar 01, 2012 10:36 AM|LINK
This link display
Page not found
sandeepmitta...
Contributor
6789 Points
1058 Posts
Re: How to convert Rows from multiple tables into Columns with values in Sql Server 2005
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 SELECT 1,1,'American' 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 SELECT 1,1,'Honkong' 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 union all 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 #tempSandeep Mittal | My Blog - IT Developer Zone
bhaskar.mule
Contributor
2270 Points
659 Posts
Re: How to convert Rows from multiple tables into Columns with values in Sql Server 2005
Mar 01, 2012 03:56 PM|LINK
you can go with this now
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
Site:Rare technical solutions
Sukhjeevan S...
Member
38 Points
51 Posts
Re: How to convert Rows from multiple tables into Columns with values in Sql Server 2005
Mar 02, 2012 05:00 AM|LINK
thanks sandeep.thanks a lot
Its exactly what i needed
Sukhjeevan S...
Member
38 Points
51 Posts
Re: How to convert Rows from multiple tables into Columns with values in Sql Server 2005
Mar 02, 2012 08:39 AM|LINK
Hi Sandeep,
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.
Thanks
sandeepmitta...
Contributor
6789 Points
1058 Posts
Re: How to convert Rows from multiple tables into Columns with values in Sql Server 2005
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
Sandeep Mittal | My Blog - IT Developer Zone
Sukhjeevan S...
Member
38 Points
51 Posts
Re: How to convert Rows from multiple tables into Columns with values in Sql Server 2005
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.