title: Inner Join Pivot SQL
body: <p>I have 2 tables, tblClient and tblDB.</p>
  
  <pre><code>CREATE TABLE tblBD(
  [ID] INT PRIMARY KEY,
  [DataBase] nVARCHAR (30) NOT NULL,
  [ClientID] INT NOT NULL,
  FOREIGN KEY ([ClientID]) REFERENCES tblClient(ID)
  )
  
  CREATE TABLE tblClient(
  [ID] INT PRIMARY KEY,
  [Name] nVARCHAR(30),
  [Status] BIT,
  [Number] INT,
  )
  </code></pre>
  
  <p>I want a way to get for each client all his databases.</p>
  
  <p>Something like this :</p>
  
  <pre><code>Client1 Client2 ClientX
  BD1     BD1     BD1 
  BD2     BD2     BD2
  BDX     BDX     BDX
  </code></pre>
  
  <p>I can do it easily for one client by executing this script :</p>
  
  <pre><code>Select tblBD.[DataBase] from tblBD
  inner join tblCLient on tblBD.ClientID = tblClient.ID Where tblClient.ID = 1234
  </code></pre>
  
  <p>But, I can't figure How to get the result for all clients in 1 table.</p>
  
  <p>For the pivot I'm not able to get the result without using an aggregate function.</p>
  
  <p>Example :</p>
  
  <pre><code>WITH T
  AS (Select tblBD.[DataBase] as BD, tblClient.ID as ClientID from
  tblBD inner join tblCLient on tblBD.ClientID = tblClient.ID)
  SELECT *
  FROM   T PIVOT ( 
  max (BD) FOR ClientID IN ([1],[2],[3], [4])
  ) AS pvt
  </code></pre>
  
  <p>I want to get all the databases without using an aggregate function.</p>