Click here to Skip to main content
11,431,829 members (54,981 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
Greetings,

Having table --table1(id,questionid,response,points)

values

1,1,'yes',4

2,1,'no',4

3,1,'copper',2

4,2,'yes',1

5,2,'yes',2

want to insert data from table1 into table2(id,ques1,point1,ques2,point2,ques3,point3,ques4,point4,ques5,point5)

insert response ,points values into coresponding question1d's


if questionid=1

insert into table2 values ques1='yes' and points=4

table2 output

id,ques1,point1,ques2,point2,ques3,point3,ques4,point4,ques5,point5

1 'yes' 4 'yes' 1 ..........


below are the scripts.please check this and any solution

CREATE TABLE [dbo].[surveyanswers]( 
[id] [int] IDENTITY(1,1) NOT NULL, 
[questionid] [nvarchar](50) NULL, 
[response] [nvarchar](50) NULL, 
[points] [nvarchar](50) NULL, 
CONSTRAINT [PK_surveyanswers] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] 
GO 

output
id questionid response points
1 1 yes 4
2 1 yes 5
3 1 no 3
4 2 no 12
5 2 yes 2
6 3 copper 14

Here questionid is ques_1 and q1build for this i want to insert response='yes' goes to ques_1 column and points =4 value goes to q1build

CREATE TABLE [dbo].[surveryanswers1]( 
[id] [int] IDENTITY(1,1) NOT NULL, 
[ques_1] [nvarchar](50) NULL, 
[q1build] [nvarchar](50) NULL, 
[ques_2a] [nvarchar](50) NULL, 
[q2abuild] [nvarchar](50) NULL, 
[ques_2b] [nvarchar](50) NULL, 
[q2bbuild] [nvarchar](50) NULL, 
[ques_2c] [nvarchar](50) NULL, 
[q2cbuild] [nvarchar](50) NULL, 
[ques_3] [nvarchar](50) NULL, 
[q3build] [nvarchar](50) NULL, 
[ques_4] [nvarchar](50) NULL, 
[qbuild] [nvarchar](50) NULL, 
[ques_5] [nvarchar](50) NULL, 
[qbuild5] [nvarchar](50) NULL, 
CONSTRAINT [PK_surveryanswers1] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO

output

id ques_1 q1build ques_2 q2build
1 yes 4 no 12
Posted 12-Nov-13 15:24pm
Edited 12-Nov-13 20:43pm
coded0077.9K
v4

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

My first thought is something along the lines of:

INSERT INTO table2 ( ... )
SELECT A.*
,B.response,B.points
...
FROM (SELECT * FROM table WHERE questionid=1) A
INNER JOIN (SELECT * FROM table WHERE questionid=2) B
ON A.questionid=B.questionid
...
  Permalink  
Comments
Member 10397661 at 12-Nov-13 21:59pm
   
below are the scripts.please check this and any solution

CREATE TABLE [dbo].[surveyanswers](
[id] [int] IDENTITY(1,1) NOT NULL,
[questionid] [nvarchar](50) NULL,
[response] [nvarchar](50) NULL,
[points] [nvarchar](50) NULL,
CONSTRAINT [PK_surveyanswers] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


output

id questionid response points

1 1 yes 4
2 1 yes 5
3 1 no 3
4 2 no 12
5 2 yes 2
6 3 copper 14




Here questionid is ques_1 and q1build for this i want to insert response='yes' goes to ques_1 column and points =4 value goes to q1build

CREATE TABLE [dbo].[surveryanswers1](
[id] [int] IDENTITY(1,1) NOT NULL,
[ques_1] [nvarchar](50) NULL,
[q1build] [nvarchar](50) NULL,
[ques_2a] [nvarchar](50) NULL,
[q2abuild] [nvarchar](50) NULL,
[ques_2b] [nvarchar](50) NULL,
[q2bbuild] [nvarchar](50) NULL,
[ques_2c] [nvarchar](50) NULL,
[q2cbuild] [nvarchar](50) NULL,
[ques_3] [nvarchar](50) NULL,
[q3build] [nvarchar](50) NULL,
[ques_4] [nvarchar](50) NULL,
[qbuild] [nvarchar](50) NULL,
[ques_5] [nvarchar](50) NULL,
[qbuild5] [nvarchar](50) NULL,
CONSTRAINT [PK_surveryanswers1] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


output

id ques_1 q1build ques_2 q2build

1 yes 4 no 12
PIEBALDconsult at 12-Nov-13 22:55pm
   
Why would I do that?
coded007 at 13-Nov-13 1:48am
   
Good idea is you can maintain a survey asnwers with questionid,response and points you got. Please recheck your table sturcture

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web03 | 2.8.150428.2 | Last Updated 13 Nov 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100