How to convert Rows from multiple tables into Columns with values in Sql Server 2005

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

Re: How to convert Rows from multiple tables into Columns with values in Sql Server 2005

Hi,

First - Create a View with all join conditions .

second - Use Pivot on that View.

Regards

Shwetamber

Re: How to convert Rows from multiple tables into Columns with values in Sql Server 2005

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

Re: How to convert Rows from multiple tables into Columns with values in Sql Server 2005

This link display

Page not found

Re: How to convert Rows from multiple tables into Columns with values in Sql Server 2005

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

Re: How to convert Rows from multiple tables into Columns with values in Sql Server 2005

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

Re: How to convert Rows from multiple tables into Columns with values in Sql Server 2005

thanks sandeep.thanks a lot

Its exactly what i needed

Re: How to convert Rows from multiple tables into Columns with values in Sql Server 2005

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

Re: How to convert Rows from multiple tables into Columns with values in Sql Server 2005

You are welcome.

There should be BCEid on UserId in mstBCE Table, otherwise you would not be able to link the data

Re: How to convert Rows from multiple tables into Columns with values in Sql Server 2005

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.

Re: How to convert Rows from multiple tables into Columns with values in Sql Server 2005

The problem is not BCE column, but the problem is that how would you link BCE data with user for display as required.

Re: How to convert Rows from multiple tables into Columns with values in Sql Server 2005

Data should display as given below :

Re: How to convert Rows from multiple tables into Columns with values in Sql Server 2005

There is a way to do it without using pivot.

CREATE TABLE TABLE1 (USERID NUMBER, USERNAME VARCHAR(10), DEPARTMENT VARCHAR(10));
CREATE TABLE TABLE2 (UNIQUEID NUMBER, USERID NUMBER, LANGUAGE VARCHAR(20));
CREATE TABLE TABLE3 (UNIQUEID NUMBER, USERID NUMBER, COUNTRYPASSPORT VARCHAR(20));

INSERT INTO TABLE1
SELECT 1, 'AA', 'TT' FROM DUAL
UNION ALL SELECT 2, 'BB', 'GG' FROM DUAL
UNION ALL SELECT 3, 'CC', 'RR' FROM DUAL
UNION ALL SELECT 4, 'DD', 'DD' FROM DUAL;

INSERT INTO TABLE2
SELECT 1,1,'AMERICAN' FROM DUAL
UNION ALL SELECT 1,1,'ARABIC' FROM DUAL
UNION ALL SELECT 1,2,'AZERBAIJANI' FROM DUAL
UNION ALL SELECT 1,3,'BULGARIAN' FROM DUAL
UNION ALL SELECT 1,3,'CHAOCHOW' FROM DUAL
UNION ALL SELECT 1,4,'BEHDINI' FROM DUAL;

INSERT INTO TABLE3
SELECT 1,1,'HONKONG' FROM DUAL
UNION ALL SELECT 1,1,'MALAYSIA' FROM DUAL
UNION ALL SELECT 1,2,'SPAIN' FROM DUAL
UNION ALL SELECT 1,3,'CHINA' FROM DUAL
UNION ALL SELECT 1,3,'INDIA' FROM DUAL
UNION ALL SELECT 1,4,'UK' FROM DUAL;

CREATE TABLE TARGET_TABLE AS
SELECT USERNAME,
       DEPARTMENT,
       (CASE
         WHEN AMERICAN = '1' THEN
          'YES'
         ELSE
          'NO'
       END) AMERICAN,
       (CASE
         WHEN ARABIC = '1' THEN
          'YES'
         ELSE
          'NO'
       END) ARABIC,
       (CASE
         WHEN AZERBAIJANI = '1' THEN
          'YES'
         ELSE
          'NO'
       END) AZERBAIJANI,
       (CASE
         WHEN BULGARIAN = '1' THEN
          'YES'
         ELSE
          'NO'
       END) BULGARIAN,
       (CASE
         WHEN CHAOCHOW = '1' THEN
          'YES'
         ELSE
          'NO'
       END) CHAOCHOW,
       (CASE
         WHEN BEHDINI = '1' THEN
          'YES'
         ELSE
          'NO'
       END) BEHDINI,
       (CASE
         WHEN HONKONG = '1' THEN
          'YES'
         ELSE
          'NO'
       END) HONKONG,
       (CASE
         WHEN MALAYSIA = '1' THEN
          'YES'
         ELSE
          'NO'
       END) MALAYSIA,
       (CASE
         WHEN SPAIN = '1' THEN
          'YES'
         ELSE
          'NO'
       END) SPAIN,
       (CASE
         WHEN INDIA = '1' THEN
          'YES'
         ELSE
          'NO'
       END) INDIA,
       (CASE
         WHEN CHINA = '1' THEN
          'YES'
         ELSE
          'NO'
       END) CHINA,
       (CASE
         WHEN UK = '1' THEN
          'YES'
         ELSE
          'NO'
       END) UK
FROM (SELECT USERNAME,
               DEPARTMENT,
               SUM(CASE
                     WHEN STATUS = 'AMERICAN' THEN
                      '1'
                     ELSE
                      '0'
                   END) AMERICAN,
               SUM(CASE
                     WHEN STATUS = 'ARABIC' THEN
                      '1'
                     ELSE
                      '0'
                   END) ARABIC,
               SUM(CASE
                     WHEN STATUS = 'AZERBAIJANI' THEN
                      '1'
                     ELSE
                      '0'
                   END) AZERBAIJANI,
               SUM(CASE
                     WHEN STATUS = 'BULGARIAN' THEN
                      '1'
                     ELSE
                      '0'
                   END) BULGARIAN,
               SUM(CASE
                     WHEN STATUS = 'CHAOCHOW' THEN
                      '1'
                     ELSE
                      '0'
                   END) CHAOCHOW,
               SUM(CASE
                     WHEN STATUS = 'BEHDINI' THEN
                      '1'
                     ELSE
                      '0'
                   END) BEHDINI,
               SUM(CASE
                     WHEN STATUS = 'HONKONG' THEN
                      '1'
                     ELSE
                      '0'
                   END) HONKONG,
               SUM(CASE
                     WHEN STATUS = 'MALAYSIA' THEN
                      '1'
                     ELSE
                      '0'
                   END) MALAYSIA,
               SUM(CASE
                     WHEN STATUS = 'SPAIN' THEN
                      '1'
                     ELSE
                      '0'
                   END) SPAIN,
               SUM(CASE
                     WHEN STATUS = 'INDIA' THEN
                      '1'
                     ELSE
                      '0'
                   END) INDIA,
               SUM(CASE
                     WHEN STATUS = 'CHINA' THEN
                      '1'
                     ELSE
                      '0'
                   END) CHINA,
               SUM(CASE
                     WHEN STATUS = 'UK' THEN
                      '1'
                     ELSE
                      '0'
                   END) UK
          FROM (SELECT A.USERNAME, DEPARTMENT, STATUS
                  FROM TABLE1 A,
                       (SELECT B.USERID, B.USERNAME, LANGUAGE AS STATUS
                          FROM TABLE2 A, TABLE1 B
                         WHERE A.USERID = B.USERID
                        UNION ALL
                        SELECT B.USERID, B.USERNAME, COUNTRYPASSPORT
                          FROM TABLE3 A, TABLE1 B
                         WHERE A.USERID = B.USERID) B
                 WHERE A.USERID = B.USERID)
         GROUP BY USERNAME, DEPARTMENT);

Thanks and Regards,

Y.V. Sivaram