Author |
Topic |
bekeer020
Starting Member
24 Posts |
Posted - 2011-09-14 : 11:07:56
|
Hello allI have table in one query like this:Stu_ID Subject_Name---------------------------1 English1 Math1 France1 Geog2 France2 Italynow i want to show it like this formatStu_ID Subject1 Subject2 Subject3 Subject4-----------------------------------------------------------------1 English Math France Geog2 France Italyi want to split data into 4 columnscan i do like this pleasenote: maximum columns is four |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-14 : 11:21:36
|
[code]SELECT Stu_ID,[1] AS Subject1,[2] AS Subject2,[3] AS Subject3,[4] AS Subject4FROM (SELECT ROW_NUMBER() OVER (PARTITION BY Stu_ID ORDER BY Subject_name) AS Seq,* FROM Table)tPIVOT(MAX(Subject_Name) FOR Seq IN ([1],[2],[3],[4]))p[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
bekeer020
Starting Member
24 Posts |
Posted - 2011-09-15 : 00:04:47
|
thank you visakhm for your solution but i have large view (from 7 tables) how i add this four columns my view like this:------------------SELECT TOP (100) PERCENT dbo.Class_Registration.Stu_ID, dbo.Students.Full_Nmae, dbo.Classes.Semester_ID, dbo.Training_Product.Training_Product_ID, dbo.Class_Registration.Class_ID, dbo.Classes.Course_ID, dbo.Courses.Subject_name, dbo.Classes.Center_IDFROM dbo.Training_Product_Registration INNER JOIN dbo.Class_Registration INNER JOIN dbo.Classes ON dbo.Class_Registration.Class_ID = dbo.Classes.Class_ID INNER JOIN dbo.Courses ON dbo.Classes.Course_ID = dbo.Courses.Course_ID INNER JOIN dbo.Semester ON dbo.Classes.Semester_ID = dbo.Semester.Semester_ID INNER JOIN dbo.Students ON dbo.Class_Registration.Stu_ID = dbo.Students.Stu_ID ON dbo.Training_Product_Registration.Registration_ID = dbo.Class_Registration.Registration_ID INNER JOIN dbo.Training_Product INNER JOIN dbo.[Plan] ON dbo.Training_Product.Training_Product_ID = dbo.[Plan].Training_Product_ID ON dbo.Training_Product_Registration.Plan_ID = dbo.[Plan].Plan_ID INNER JOIN dbo.View_man ON dbo.Class_Registration.Stu_ID = dbo.View_man.Stu_IDWHERE (dbo.Classes.Semester_ID = N'20101') AND (dbo.Classes.Center_ID = N'41QAM') AND (dbo.Training_Product.Training_Product_ID = 9)------------------note:also in above query client_ID and Course_Arabic_Name in different tablehow can add four columns (subject1,subject2,subject3,subject4)to distribute subject_names for each client if my data like in my query |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-15 : 01:06:14
|
you want all these columns to appear along with transpose? can you show waht should be your required output with all the columns?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-15 : 01:24:17
|
Can you please give requested output format?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
bekeer020
Starting Member
24 Posts |
Posted - 2011-09-15 : 01:33:11
|
see i will give you easy example suppose i have this three tablesStudent------St_IDSt_NameData:1 John2 Smith---------------------------------------------------------------Courses------Cu_IDCu_NameData:A1 OutlookA2 ExcelA3 FlashA4 Access---------------------------------------------------------------Class_Register------St_IDCU_IDData:1 A11 A21 A31 A42 A22 A2---------------------------------------------------------------also there is relation between this tablesI want output like this:St_ID St_Name Subject1 Subject2 Subject3 Subject4----------------------------------------------------------------------------1 John Outlook Excel Flash Access2 Smith Excel Flashthe data of Subject1..4 from Courses table depend on data in class_Register table |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-15 : 01:41:54
|
See for the scenario I've given you solution already using PIVOT.But regarding your actual view my question was whether you need to return any more additional columns other that id,name and 4 subjects as i see lot of other columns also in view definition.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
bekeer020
Starting Member
24 Posts |
Posted - 2011-09-15 : 02:01:12
|
can you give me your email i will send sample from my DB |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-15 : 02:45:35
|
no need. please post your sample output here giving 5 or 10 rows from view------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
bekeer020
Starting Member
24 Posts |
Posted - 2011-09-15 : 03:28:20
|
see in the above picture this is my real view the out put three columns client_id this student id full_Arab_Name this is student nameno need to this column (Course_Eng_Name) ,I want Subject1 Subject2 Subject3 Subject4 for each Student(client_id) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-15 : 03:59:19
|
I cant see the picture. anyways i think it should be something likeSELECT Stu_ID,Full_Nmae,MAX(CASE WHEN Seq=1 THEN Subject_name ELSE NULL END) AS Subject1,MAX(CASE WHEN Seq=2 THEN Subject_name ELSE NULL END) AS Subject2,MAX(CASE WHEN Seq=3 THEN Subject_name ELSE NULL END) AS Subject3,MAX(CASE WHEN Seq=4 THEN Subject_name ELSE NULL END) AS Subject4FROM(SELECT ROW_NUMBER() OVER (PARTITION BY dbo.Class_Registration.Stu_ID,dbo.Students.Full_Nmae ORDER BY dbo.Courses.Subject_name) AS Seq, dbo.Class_Registration.Stu_ID, dbo.Students.Full_Nmae, dbo.Classes.Semester_ID, dbo.Training_Product.Training_Product_ID, dbo.Class_Registration.Class_ID, dbo.Classes.Course_ID, dbo.Courses.Subject_name, dbo.Classes.Center_IDFROM dbo.Training_Product_Registration INNER JOINdbo.Class_Registration INNER JOINdbo.Classes ON dbo.Class_Registration.Class_ID = dbo.Classes.Class_ID INNER JOINdbo.Courses ON dbo.Classes.Course_ID = dbo.Courses.Course_ID INNER JOINdbo.Semester ON dbo.Classes.Semester_ID = dbo.Semester.Semester_ID INNER JOINdbo.Students ON dbo.Class_Registration.Stu_ID = dbo.Students.Stu_ID ON dbo.Training_Product_Registration.Registration_ID = dbo.Class_Registration.Registration_ID INNER JOINdbo.Training_Product INNER JOINdbo.[Plan] ON dbo.Training_Product.Training_Product_ID = dbo.[Plan].Training_Product_ID ON dbo.Training_Product_Registration.Plan_ID = dbo.[Plan].Plan_ID INNER JOINdbo.View_man ON dbo.Class_Registration.Stu_ID = dbo.View_man.Stu_IDWHERE (dbo.Classes.Semester_ID = N'20101') AND (dbo.Classes.Center_ID = N'41QAM') AND (dbo.Training_Product.Training_Product_ID = 9))tGROUP BY Stu_ID,Full_Nmae ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
bekeer020
Starting Member
24 Posts |
Posted - 2011-09-15 : 08:53:11
|
not correct .. This is my dataThere is view "view15" edite it to my goal and give me the code |
|
|
bekeer020
Starting Member
24 Posts |
Posted - 2011-09-15 : 22:08:29
|
Can any one help me----------------------This is my dataThere is view "view15" edite it to my goal and give me the code |
|
|
namman
Constraint Violating Yak Guru
285 Posts |
Posted - 2011-09-15 : 23:51:06
|
Student------St_IDSt_NameData:1 John2 Smith---------------------------------------------------------------Courses------Cu_IDCu_NameData:A1 OutlookA2 ExcelA3 FlashA4 Access---------------------------------------------------------------Class_Register------St_IDCU_IDData:1 A11 A21 A31 A42 A22 A2---------------------------------------------------------------also there is relation between this tablesI want output like this:St_ID St_Name Subject1 Subject2 Subject3 Subject4----------------------------------------------------------------------------1 John Outlook Excel Flash Access2 Smith Excel FlashWith that example, you can easily solve it, then apply to your real problem.Join the 3 tables (using cte), then using case, group by, row_number, max to display the desired result. |
|
|
bekeer020
Starting Member
24 Posts |
Posted - 2011-09-17 : 14:58:17
|
Ok I solved Thank you every one for help me |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-18 : 03:13:30
|
quote: Originally posted by bekeer020 Ok I solved Thank you every one for help me
what was change you did to make it work?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|