Joining two SQL queries with different variations of data

Question

I have this query which returns some data:

select 
    id, name, username, email, password, 
    first_name, last_name, usertype,
    block, sendemail, registerDate, lastvisitDate,
    activation, params, uuid 
from 
    jml2_users 
where 
    uuid in ('51840915-e570-430d-9911-7247d076f6e7', '51912193-6694-4ca5-94c9-9f31d076f6e7',
             '51927ada-6370-4433-8a06-30d2d076f6e7', '51c05ad7-d1d0-4eb6-bc6b-424bd076f6e7',
             'd047adf1-a6af-891e-94a2d0b225dcd1b6', '2aba38f2-d7a7-0a7a-eff2be3440e3b763')

and the other query is this

SELECT 
    ct.TrainingID, ct.UserID, ct.TrainingType, ct.TrainingStatus, 
    ct.TrainingScore, ct.TrainingDate, 
    dbo.fn_StripCharacters(ctt.product_type,'^a-z0-9') as product_type, 
    ctt.product_type as oldName 
FROM 
    clientTraining as ct 
INNER JOIN 
    clientTraningTypes as ctt ON ct.TrainingType = ctt.TypeID 
WHERE 
    1=1 
    AND UserID IN ('51840915-e570-430d-9911-7247d076f6e7', '51927ada-6370-4433-‌8a06-30d2d076f6e7') 
    AND TrainingType IN (SELECT TypeID 
                         FROM complaincetestlinks 
                         WHERE parent_client_id = 1039  
                           AND isactive = 1 AND isdeleted = 0)

Both queries return different results, with userid and uuid in both tables have same data, I mean we can do a join, but the problem is: I want the second query data which are rows should be converted to columns in the new query and the data should be copied to new query with a join to the second query based on userid and uuid

I am noob at SQL and first question comes to me how do I convert the first query row data to columns and populate it with data because the first query has 5 rows. So eventually I need 5*4 = 20 columns for the new query and the data to be passed and uploaded to the new query from the second query

Not sure what I need to do here, I am lost

Will the number of columns required in the second query be fixed and not change? Will a new column ever appear? This is a _crosstab_ or _pivot_ question. SO gets about six of these a day. So your first step is to look up how to do a pivot in T-SQL — Nick.Mc, Aug 21 '16 at 23:46
i seriously do not know what is pivot, i think after this project is over, i will look around it, yes data may change, like rows will increase or decrease but columns will stay same for 2nd query — , Aug 21 '16 at 23:51
pivot is a keyword you type into google to find your answer. Like this: http://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server. You need to explain properly what you need and supply sample output or at least tell us which column needs to be pivotted. Half the reason you're lost is maybe because you don't know what the output will look like. If you provide sample output you may be a bit less lost. — Nick.Mc, Aug 22 '16 at 00:11
check this image: http://prntscr.com/c8ph42 . I want to convert the rows to the columns, but i can ignore columns: trainingID,userID,TrainingType. the every row converted should have status,Score,Date,OldName as Different rows too — , Aug 22 '16 at 00:44
Your image doesn't tell me anything new (and images have a way of disappearing over time). The image is just your second query. Please post a sample of the _output_ that you want. i.e. list the actual columns — Nick.Mc, Aug 22 '16 at 00:47
I don't see any other image.... so I still don't see any sample output. Please don't add images anymore. Please edit your question and put the sample data in there. — Nick.Mc, Aug 22 '16 at 01:02
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/121494/discussion-between-jack-and-nick-mcdermaid). — , Aug 22 '16 at 01:06

score 0 · Answer 1 · answered Aug 22 '16 at 01:40

Here is an example of how to pivot the supplied query on product_type. I've only done some of the columns. Hopefully you can follow the example to add more.

SELECT
TrainingID, 
UserID, 
TrainingType, 
TrainingStatus, 
MAX(CASE 
   WHEN product_type = 'MarketLinkedCDs'  THEN TrainingDate ELSE NULL 
END)  TrainingDate_MarketLinkedCDs,
MAX(CASE 
   WHEN product_type = 'StructuredNotes'  THEN TrainingDate ELSE NULL 
END)  TrainingDate_StructuredNotes,
MAX(CASE 
   WHEN product_type = 'BufferedRangeAccrualNotes'  THEN TrainingDate ELSE NULL 
END)  TrainingDate_BufferedRangeAccrualNotes,

MAX(CASE 
   WHEN product_type = 'MarketLinkedCDs'  THEN TrainingScore ELSE NULL 
END)  TrainingScore_MarketLinkedCDs,
MAX(CASE 
   WHEN product_type = 'StructuredNotes'  THEN TrainingScore ELSE NULL 
END)  TrainingScore_StructuredNotes,
MAX(CASE 
   WHEN product_type = 'BufferedRangeAccrualNotes'  THEN TrainingScore ELSE NULL 
END)  TrainingScore_BufferedRangeAccrualNotes

FROM
(
    SELECT ct.TrainingID, ct.UserID, ct.TrainingType, ct.TrainingStatus, 
    ct.TrainingScore, 
    ct.TrainingDate, dbo.fn_StripCharacters(ctt.product_type,'^a-z0-9') as product_type, 
    ctt.product_type as oldName FROM clientTraining as ct INNER JOIN 
    clientTraningTypes as ctt ON ct.TrainingType = ctt.TypeID WHERE 1=1 and 
    UserID in ('51840915-e570-430d-9911-7247d076f6e7',
    '51927ada-6370-4433-‌8a06-30d2d076f6e7') 
    and TrainingType IN (select TypeID from complaincetestlinks where 
    parent_client_id = 1039 and isactive = 1 and isdeleted = 0)
) F

GROUP BY
TrainingID, 
UserID, 
TrainingType, 
TrainingStatus,

Joining two SQL queries with different variations of data

1 Answers1