How to nsert data to Dynamic column of temporary table.

Question

How to insert Data in the required formate which I have shown in the "OUTPUT".

Here "OUTPUT" is my temporary table.

---

prince16in2002

Answer 1

Hi,

Looks like a t-sql question, if you want to create this using t-sql then its better to post this in t-sql forum.

You can do this using dynamic Pivot as shown below

--Sample Data

Create table #table1 
( id int
  ,name nvarchar(100)
  )
  
insert into #table1
select 1, 'John'
union all
select 2, 'Sam'
union all
select 3, 'Krishna'

Create table #table2
(
parentID int identity(1,1)
,child_id int
, project nvarchar(100)
)

insert into #table2 (child_id,project)
select 1,'DOW'
union all
select 1,'test'
union all
select 1,'BOA'
union all
select 1,'SBI'
union all
select 1,'AM'
union all
select 2,'NB'
union all
select 2,'NSE'
union all
select 2,'TAX'
union all
select 3,'JNJ'

--Actual Query
declare @columns nvarchar(max)
declare @string nvarchar(max)

;with Cte
as
( 
    select distinct row_number() over (partition by child_id order by child_id) as rn
    from #table2
) 
   
select @columns = (select quotename('project_'+ cast(rn as nvarchar(100)),']')+','
from CTE
for xml path(''))

select @columns = left(@columns, len(@columns) -1)

select @string = 'select * 
from
(
select a.id
, a.name
, ''project_'' + cast(row_number() over (partition by b.child_id order by b.child_id) as nvarchar(100))as rn
, b.project 
from #table1 a
inner join #table2 b on a.id = b.child_id
) a
pivot
(
max(project) for 
rn in (' +@columns + ')
)b
order by id '

exec(@string)

---

- Chintak (My Blog)

Answer 2

Thanks Chintak,the solution which u gave provides me exaclty the same Output as I want , But now again I am encountering with the problem with adding a new row and inserinting data in that new row .

Below Print Screen I am sending, kindly help me with this too, I am using again SQL 2005

OUTPUT_1 is more preferable as here assecending order of year is there.

My requirement is

---

prince16in2002

Answer 3

If you are using MS SQL Server 2008 R2 or 2012, take a look at PowerPivot: http://msdn.microsoft.com/en-us/library/ff628113(v=sql.100).aspx

Answer 4

I am Using MS Sql 2005

---

prince16in2002

Answer 5

I am Using MS Sql 2005

---

prince16in2002

pivot can be done in sql 2005 also.http://msdn.microsoft.com/en-us/library/ms177410(v=sql.90).aspx.

you can use CTE http://msdn.microsoft.com/en-us/library/ms190766(v=sql.90).aspx if you wish.

OR prepare sub set of tables for each Emp_ID with Project, Year separately and apply left join with main table.

regards

joon

Answer 6

use pivot function to convert rows into columns 
-- Inserting Data into Table
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','VEG',2)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','SODA',6)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','MILK',1)

SELECT PRODUCT, FRED, KATE
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT (SUM(QTY) FOR CUST IN (FRED, KATE)) AS pvt
ORDER BY PRODUCT

---

SQL Champ
Database Consultants NY

Answer 7

Thanks SQL Champ for your solution, but the solution which u have given is not fullfilling my requirement, if u look at the above figure the output which I want need some kind of solutio given by "Chintak". Solution given by Chintak is giving me the exact out that I want, but the new problem that I am facing now is that when how to add new columns in that and how to put data in that new respective columns as well as respective rows.

Kindly see the above snap short. I am using SQL server 2005.

---

prince16in2002

Answer 8

Hi,

Kindly check this

--Sample Data
if object_id('tempdb..#table1') is  not null
    drop table #table1
if object_id('tempdb..#table2') is  not null
    drop table #table2
    
Create table #table1 
( id int
  ,name nvarchar(100)
  )
  
insert into #table1
select 1, 'John'
union all
select 2, 'Sam'
union all
select 3, 'Krishna'
Create table #table2
(
parentID int identity(1,1)
,child_id int
, project nvarchar(100)
, year int
)
insert into #table2 (child_id,project,year)
select 1,'DOW', 2005
union all
select 1,'test', 2006
union all
select 1,'BOA', 2007
union all
select 1,'SBI', 2008
union all
select 1,'AM', 2009
union all
select 2,'NB',2007
union all
select 2,'NSE' , 2005
union all
select 2,'TAX', 2009
union all
select 3,'JNJ', 2009
--Actual Query
declare @columns nvarchar(max)
declare @columns1 nvarchar(max)
declare @columnsRet nvarchar(max)
declare @string nvarchar(max)
;with Cte
as
( 
    select distinct row_number() over (partition by child_id order by child_id) as rn
    from #table2
) 
   
select @columns = (select quotename('project_'+ cast(rn as nvarchar(100)),']')+','
from CTE
for xml path(''))
select @columns = left(@columns, len(@columns) -1)
;with Cte1
as
( 
    select distinct row_number() over (partition by child_id order by child_id) as rn
    from #table2
) 
   
select @columns1 = (select quotename('Year_'+ cast(rn as nvarchar(100)),']')+','
from CTE1
for xml path(''))
select @columns1 = left(@columns1, len(@columns1) -1)
;with Cte2
as
( 
    select distinct row_number() over (partition by child_id order by child_id) as rn
    from #table2
) 
   
select @columnsRet = (select 'max(' + 'year_'+ cast(rn as nvarchar(100))+')' + ' as year_' + cast(rn as nvarchar(100)) 
+ ','
+ 'max(' + 'project_'+ cast(rn as nvarchar(100))+')' + ' as project_' + cast(rn as nvarchar(100)) +','
from CTE2
for xml path(''))
select @columnsRet = left(@columnsRet, len(@columnsRet) -1)
select @string = 'select id,name,' + @columnsRet +' 
from
(
select a.id
, a.name
, ''project_'' + cast(row_number() over (partition by b.child_id order by b.child_id) as nvarchar(100))as rn
, ''year_'' + cast(row_number() over (partition by b.child_id order by b.child_id) as nvarchar(100))as rn1
, b.project 
, b.year 
from #table1 a
inner join #table2 b on a.id = b.child_id
) a
pivot
(
max(project) for 
rn in (' +@columns + ')
)b
pivot
(
max(year) for 
rn1 in (' +@columns1 + ')
)c
group by id,name
order by id '
exec(@string)

---

- Chintak (My Blog)

Answer 9

What is the question?

I see that you have dynamic SQL to produce a dynamic crosstab. Once you you have prodcued this result, return it to the client, and to try do anything more with it in SQL Server, unless you really like to hurt yourself.

---

Erland Sommarskog, SQL Server MVP, [e-mail]