How to combine two select statements result column wise? I have two select queries and i want to combine result of both column wise.I have use union between two select queries but it give me just one query result



I want expected result like this 



My SQL QUERY

    SELECT TC.DESCRIPTION, count(TE.CategoryID) AS COUNT 
   FROM tblCategory TC 
    LEFT OUTER JOIN tblEvent TE
    on TE.CategoryID=TC.NO
    AND TE.AssetID IN (
                        SELECT ASSET_NO 
                        FROM tblAsset 
                        WHERE EQUIPMENT_ID=1
                        )
  GROUP BY  TE.CategoryID,TC.DESCRIPTION 

  UNION 
  SELECT TC.DESCRIPTION as desc2, count(TE.CategoryID) AS COUNT2
  FROM tblCategory TC 
  LEFT OUTER JOIN tblEvent TE
    on TE.CategoryID=TC.NO
    AND TE.AssetID IN (
                        SELECT ASSET_NO 
                        FROM tblAsset 
                        WHERE EQUIPMENT_ID=2
                        )
  GROUP BY  TE.CategoryID,TC.DESCRIPTION
Selecting UNION will append the results below the existing result set.
If you want the second query's results as an additional column you will need to write it as a single query and JOIN the additional column.

You can do this by including the 'second' query as a subquery and joining the results together.
Without changing your existing query structure:

SELECT      count1.DESCRIPTION,
            count1.[COUNT],
            count2.COUNT2
FROM
(
    SELECT TC.DESCRIPTION,
                count(TE.CategoryID) AS COUNT
    FROM tblCategory TC 
     LEFT OUTER JOIN tblEvent TE
            on TE.CategoryID=TC.NO
            AND TE.AssetID IN (
                            SELECT ASSET_NO 
                            FROM tblAsset 
                            WHERE EQUIPMENT_ID=1
                                )
    GROUP BY  TE.CategoryID,TC.DESCRIPTION 
) count1
LEFT OUTER JOIN
(
    SELECT TC.DESCRIPTION as desc2, count(TE.CategoryID) AS COUNT2
    FROM tblCategory TC 
    LEFT OUTER JOIN tblEvent TE
        on TE.CategoryID=TC.NO
        AND TE.AssetID IN (
                        SELECT ASSET_NO 
                        FROM tblAsset 
                        WHERE EQUIPMENT_ID=2
                        )
    GROUP BY  TE.CategoryID,TC.DESCRIPTION 
) count2 ON count2.desc2 = count1.DESCRIPTION


Of course, this assumes your value for TC.DESCRIPTION is unique, otherwise you will potentially generate duplicate rows. If you have a primary key to join in instead this is preferable, however the underlying table structure was not given in the question.