Populating Rows result to column

Other SQL Server Topics
1

Hi there,

Can anyone help me, how to this, I want to populate rows result to column, please see attached image.

TIA

Populate Columns.jpg706×583 251 KB

2

where is the values "Chai", "10 boxes x 20 bags" etc comes from ?

3

Query result from CMAP

TMAP (Table Mapper)
TableID varchar(50),
oTableName varchar(100) NOT NULL,
nTableName varchar(100) NOT NULL,
gTableName varchar(100)

CMAP (Column Mapper)
ColumnID varchar(50) CONSTRAINT DF_CMAP_ColumnID DEFAULT newid(),
TableID varchar(50) NOT NULL,
fTableName varchar(100) NOT NULL,
oColumnName varchar(100) NOT NULL,
nColumnName varchar(100),

4

Hi @emesty

Try this

create table CMAP(
ColumnID varchar(50) CONSTRAINT DF_CMAP_ColumnID DEFAULT newid(),
TableID varchar(50) NOT NULL,
fTableName varchar(100) NOT NULL,
oColumnName varchar(100) NOT NULL,
nColumnName varchar(100))

insert into CMAP
select '1' as ColumnID,'20150914084812' as TableID,'Products' as fTableName,'ProductID' as oColumnName, '1' as nColumnName
union all
select '1' as ColumnID,'20150914084812' as TableID,'Products' as fTableName,'ProductName' as oColumnName, 'Chai' as nColumnName
union all
select '1' as ColumnID,'20150914084812' as TableID,'Products' as fTableName,'QuantityPerUnit' as oColumnName, '10 boxes/ 20bags' as nColumnName
union all
select '1' as ColumnID,'20150914084812' as TableID,'Products' as fTableName,'UnitPrice' as oColumnName, '18.00' as nColumnName
union all
select '1' as ColumnID,'20150914084812' as TableID,'Products' as fTableName,'UnitsInStock' as oColumnName, '39' as nColumnName

Union all

select '2' as ColumnID,'20150914084812' as TableID,'Products' as fTableName,'ProductID' as oColumnName, '2' as nColumnName
union all
select '2' as ColumnID,'20150914084812' as TableID,'Products' as fTableName,'ProductName' as oColumnName, 'Chang' as nColumnName
union all
select '2' as ColumnID,'20150914084812' as TableID,'Products' as fTableName,'QuantityPerUnit' as oColumnName, '24 - 12 oz bottles' as nColumnName
union all
select '2' as ColumnID,'20150914084812' as TableID,'Products' as fTableName,'UnitPrice' as oColumnName, '19.00' as nColumnName
union all
select '2' as ColumnID,'20150914084812' as TableID,'Products' as fTableName,'UnitsInStock' as oColumnName, '17' as nColumnName

Union all

select '3' as ColumnID,'20150914084812' as TableID,'Products' as fTableName,'ProductID' as oColumnName, '3' as nColumnName
union all
select '3' as ColumnID,'20150914084812' as TableID,'Products' as fTableName,'ProductName' as oColumnName, 'Aniseed Syrup' as nColumnName
union all
select '3' as ColumnID,'20150914084812' as TableID,'Products' as fTableName,'QuantityPerUnit' as oColumnName, '12 - 550ml bottles' as nColumnName
union all
select '3' as ColumnID,'20150914084812' as TableID,'Products' as fTableName,'UnitPrice' as oColumnName, '10.00' as nColumnName
union all
select '3' as ColumnID,'20150914084812' as TableID,'Products' as fTableName,'UnitsInStock' as oColumnName, '13' as nColumnName

---Original------------------
select * from CMAP

--Pivot-----------------

select

[ProductID]
,[ProductName]
,[QuantityPerUnit]
,[UnitPrice]
,[UnitsInStock]
from (
select * from CMAP ) main pivot(

max(nColumnName) for oColumnName in (

[ProductID]
,[ProductName]
,[QuantityPerUnit]
,[UnitPrice]
,[UnitsInStock])) pt

/*
drop table CMAP
*/

5

@Muj9 TYVM

But I want some dynamic, the TMAP is my table mapper and my CMAP is my column mapper
so I if I add columns in CMAP I can automatically query those rows into column

actually that rows will serve as ALIAS and i'l query the rows from oColumn

6

The nColumn will become my row header, so if I add rows in CMAP means additional column or header

Product ID | Product Name | Quantity Per Unit | Unit Price | Unit in Stock

7 
DECLARE @sql NVARCHAR(max)

SELECT @sql = NULL

SELECT @sql = isnull(@sql + ',', 'SELECT ') + quotename(c.oColumnName) + ' as ' + quotename(c.nColumnName)
FROM TMAP t
INNER JOIN CMAP c ON t.TableID = c.TableID
WHERE t.oTableName = 'Products'

SELECT @sql = @sql + CHAR(13) + 'FROM ' + quotename(t.nTableName)
FROM TMAP t
WHERE t.oTableName = 'Products'

PRINT @sql

EXEC (@sql)
8

@khtan TYVM you saved me \o/