Hi there,
Can anyone help me, how to this, I want to populate rows result to column, please see attached image.
TIA
where is the values "Chai", "10 boxes x 20 bags" etc comes from ?
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),
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
*/
@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
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
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)