ranjith23 Posted September 15, 2014 Report Posted September 15, 2014 Hi All: Have a table PERSON_LANGUAGES_TBL with below Output: Output: PERSON_ID LANGUAGES 1. A5682 English 2. A5682 Spanish 3. A5682 NULL 4. A5682 NULL 5. A5682 English 6. A5682 Arabic 7. A5682 English 8. A5682 Spanish 9. A5682 NULL Requirement: I need to build a view(PERSON_LANGUAGES_TBL_VW) using above table and the output needs to be in below format: Output: PERSON_ID LANGUAGES ALL_LANGUAGES 1. A5682 English English, Spanish, Arabic 2. A5682 Spanish English, Spanish, Arabic 3. A5682 NULL English, Spanish, Arabic 4. A5682 NULL English, Spanish, Arabic 5. A5682 English English, Spanish, Arabic 6. A5682 Arabic English, Spanish, Arabic 7. A5682 English English, Spanish, Arabic 8. A5682 Spanish English, Spanish, Arabic 9. A5682 NULL English, Spanish, Arabic We are using SQL server...Not oracle Thanks in ADVANCE
Maanikanth Posted September 15, 2014 Report Posted September 15, 2014 Bhayya e query copy chesi direct ga ssms lo run cheyi ...amaina theda unte pm me DROP TABLE #temp CREATE TABLE #temp ( id INT , personid VARCHAR(100) , languages VARCHAR(100) ) INSERT INTO #temp VALUES ( 1, 'A5682', 'English' ) , ( 2, 'A5682', 'Spanish' ) , ( 3, 'A5682', NULL ) , ( 4, 'A5682', NULL ) , ( 5, 'A5682', 'English' ) , ( 6, 'A5682', 'Arabic' ) , ( 7, 'A5682', 'English' ) , ( 8, 'A5682', 'Spanish' ) , ( 9, 'A5682', NULL ) , ( 10, 'A5000', 'English' ) SELECT DISTINCT languages , personid INTO #temp2 FROM #temp WHERE languages IS NOT NULL GROUP BY personid , languages SELECT DISTINCT a.personid , alllanguages = STUFF(( SELECT ',' + b.languages FROM #temp2 b WHERE a.personid = b.personid FOR XML PATH('') ), 1, 1, '') into #temp3 FROM #temp2 a SELECT t.personid,t.languages,t3.alllanguages from #temp t join #temp3 t3 on t.personid = t3.personid
Recommended Posts