Join Tables with Rows

How to join tables in rows instead of columns?

Try this:

SELECT id, Type FROM table1
UNION ALL
SELECT id, Type FROM table2
ORDER BY ID,TYPE

Result:

ID  TYPE
1   A
1   B
1   C
2   B
2   D
2   E

See result in SQL Fiddle.

SQL (simply) Join two tables row by row

Something like this..

SELECT *
FROM (
  SELECT row_number() OVER (), *
  FROM tbl1
  -- ORDER BY id (or whatever you're ordering by)
) AS t1
INNER JOIN (
  SELECT row_number() OVER (), *
  FROM tbl2
  -- ORDER BY id (or whatever you're ordering by)
) AS t2
  USING (row_number);

Join tables with rows

If you have only a few numbers of positions, you can do it with PIVOT keyword

select
    UserID,
    UserName,
    [1] as Position1,
    [2] as Position2,
    [3] as Position3
from
(
    select
        U.UserID, U.UserName, P.PositionName,
        row_number() over (partition by U.UserID order by P.PositionName) as RowNum
    from Positions_Users as PU
        inner join Positions as P on P.PositionID = PU.PositionID
        inner join Users as U on U.UserID = PU.UserID
) as P
    pivot
    (
        min(P.PositionName)
        for P.RowNum in ([1], [2], [3])
    ) as PIV

SQL FIDDLE

If, however, you want to have a dynamic number of columns, you have to use dynamic SQL, like this

declare @stmt nvarchar(max), @stmt_columns1 nvarchar(max), @stmt_columns2 nvarchar(max)
declare @Temp_Data table (RowNum nvarchar(max))

insert into @Temp_Data
select distinct row_number() over (partition by U.UserID order by P.PositionName) as RowNum
from Positions_Users as PU
    inner join Positions as P on P.PositionID = PU.PositionID
    inner join Users as U on U.UserID = PU.UserID

select @stmt_columns1 = stuff((select ', [' + RowNum + ']' from @Temp_Data for xml path(''), type).value('.', 'nvarchar(max)'), 1, 2, '')
select @stmt_columns2 = stuff((select ', [' + RowNum + '] as Position' + RowNum from @Temp_Data for xml path(''), type).value('.', 'nvarchar(max)'), 1, 2, '')

select @stmt = '
select
    UserID,
    UserName,' + @stmt_columns2 + '
from
(
    select
        U.UserID, U.UserName, P.PositionName,
        row_number() over (partition by U.UserID order by P.PositionName) as RowNum
    from Positions_Users as PU
        inner join Positions as P on P.PositionID = PU.PositionID
        inner join Users as U on U.UserID = PU.UserID
) as P
    pivot
    (
        min(P.PositionName)
        for P.RowNum in (' + @stmt_columns1 + ')
    ) as PIV'

exec sp_executesql @stmt = @stmt

SQL FIDDLE

SQL - JOIN TABLE A AND TABLE B SO THAT EACH ROW STATES WHICH TABLE IT COMES FROM, A, B OR BOTH

You can union the rows from both tables (with an added column indicating where each row came from) then group by and group_concat(origin) to check if the row exists in either or both tables:

select id, value, 
case group_concat(origin order by origin)
when 'A' then 'A'
when 'B' then 'B'
when 'A,B' then 'BOTH'
end as origin from
(select *, 'A' as origin from A
union all 
select *, 'B' as origin from B) t
group by id, value

Fiddle

(Assuming a couple (id, value) cannot appear more than once in each table)

Combine 2 rows of different tables into one row in SQL?

What you are looking for is a table join, in this case an INNER JOIN between table1 and table2 where the email column matches.

SELECT table1.email, table1.name, table1.last_name, table1.activated, table2.status, table2.end_period, table2.qtd
FROM `table1` AS table1
INNER JOIN `table2` AS table2
ON table1.email = table2.email
WHERE table1.email = 'ex@ex.com';

This results in your expected output:

SELECT [Fullname], [Email], [Position], [Category]
FROM (
    SELECT i.ColumnValue, a.[Name],
           ROW_NUMBER() OVER (PARTITION BY a.[Name] ORDER BY a.ColumnNameID) AS rn
    FROM ColumnValues i
    INNER JOIN ColumnNames a
      ON i.ColumnNameID = a.ColumnNameID
  ) tbl

PIVOT
   (MAX(ColumnValue) 
    FOR [Name] IN ([Fullname], [Email], [Position], [Category])
   ) pvt
ORDER BY rn

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.[Name]) 
            FROM ColumnNames c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
             ,1,1,'')

SET @query = 'SELECT ' + @cols + '
                FROM (
                    SELECT i.ColumnValue, a.[Name],
                           ROW_NUMBER() OVER (PARTITION BY a.[Name] ORDER BY a.ColumnNameID) AS rn
                    FROM ColumnValues i
                    INNER JOIN ColumnNames a
                      ON i.ColumnNameID = a.ColumnNameID
                  ) tbl

                PIVOT
                   (MAX(ColumnValue) 
                    FOR [Name] IN (' + @cols + ')
                   ) pvt
                ORDER BY rn'

EXECUTE(@query)

select a.ID, string_agg(CatCol, '' order by if(CatCol = 'blue', 1, 2) limit 1) CatCol
from table_a a left join table_b b
on a.ID = b.ID and CatCol in ('blue', 'green')
group by ID                

   select t1.id, 
          t2.name book, 
          t3.name page, 
          t4.name line
    from tbl t1
    join treeTbl t2
      on t1.book = t2.id
    join treeTbl t3
       on t3.id=t1.page
    join treeTbl t4
       on t4.id=t1.line