How to convert rows into columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to convert rows into columns

Author

Topic

Nir_parekh
Starting Member

3 Posts

Posted - 2008-08-01 : 10:50:07

Hi,

I have this table.

#######################################################################################

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Tracking](

[Tracking_ID] [int] IDENTITY(1,1) NOT NULL,

[Respondent_ID] [int] NOT NULL,

[Survey_ID] [int] NOT NULL,

[Question_ID] [int] NOT NULL,

[Time_Stamp] [datetime] NOT NULL CONSTRAINT [DF_Tracking_Time_Stamp] DEFAULT (getdate()),

[strURL] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[blnSuccess] [bit] NULL CONSTRAINT [DF_Tracking_blnSuccess] DEFAULT (0),

[strParentURL] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Seq] [int] NULL CONSTRAINT [DF_Tracking_Seq] DEFAULT (0),

[strTitle] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

CONSTRAINT [PK_Tracking] PRIMARY KEY CLUSTERED

(

[Tracking_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

SET ANSI_PADDING OFF

GO

#######################################################################################

There are my datas in table.

#######################################################################################

INSERT INTO TABLE1 VALUES

(

296361,1154,6,'2005-09-06 12:11:42.903','http://www.jnj.com/home.htm',0,NULL,0,'Johnson & Johnson'

)

INSERT INTO TABLE1 VALUES

(

296361,1154,6,'2005-09-06 12:12:03.823','http://www.jnj.com/home.htm',0,NULL,0,'Johnson & Johnson'

)

INSERT INTO TABLE1 VALUES

(

296361,1154,6,'2005-09-06 12:11:42.340','http://www.jnj.com/student_resources/index.htm;jsessionid=TJQTNMSOU531QCQPCCFSZOYKB2IIQNSC',0,NULL,0,'Johnson & Johnson'

)

INSERT INTO TABLE1 VALUES

(

296361,1154,6,'2005-09-07 17:08:58.933','http://www.jnj.com/home.htm',0,NULL,0,'Johnson & Johnson'

)

INSERT INTO TABLE1 VALUES

(

296363,1154,6,'2005-09-06 12:14:26.793','http://www.jnj.com/home.htm',0,NULL,0,'Johnson & Johnson'

)

INSERT INTO TABLE1 VALUES

(

309205,1154,6,'2005-09-20 08:22:15.570','http://www.jnj.com/home.htm',0,NULL,0,'Johnson & Johnson'

)

INSERT INTO TABLE1 VALUES

(

311547,1154,6,'2005-09-22 14:33:39.640','http://www.jnj.com/home.htm',0,NULL,0,'Johnson & Johnson'

)

INSERT INTO TABLE1 VALUES

(

311547,1154,6,'2005-09-22 14:36:11.000','http://www.jnj.com/home.htm',0,NULL,0,'Johnson & Johnson'

)

#######################################################################################

Now I want to generate an output like this.

1. it should be grouped by "responsent_id, question_id"

2. strURL needs to be shown as Columns for each "responsent_id, question_id"

3. for example, if there are 6 records for a particular "responsent_id, question_id", it should show as a single row only, containing responsent_id, question_id, and six columns to be shown as strURL. Same for each "responsent_id, question_id", whatever number of records found for it, it should show all strURL for that as columns and should represent only a single row.

I tried using Pivot by finding some examples from google but somehow couldnt complete it as was facing different problems with each example.

Can you please tell me how to achieve this?

Thanks,

Nirav

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-01 : 10:58:39

See if this helps
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Madhivanan

Failing to plan is Planning to fail

Nir_parekh
Starting Member

3 Posts

Posted - 2008-08-01 : 13:27:54

Hi, Thanks for this link.
But still I couldnot make query for as per my requirement.
as per my requirement, I can have dynamic columns.

Can you give me the exact query for my table? That will really help me.

Thanks again,
Nirav

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-01 : 13:31:57

didnt understand that. will strURL be having same set of values for each responsent_id, question_id group?

Nir_parekh
Starting Member

3 Posts

Posted - 2008-08-01 : 13:46:11

No. strURL will be having different values gererally for each respondent_id, question_id.

But I just got answer for this in another microsoft forum.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3695085&SiteID=1

But thanks for your help and do let me know if you have any other ways as well to do this.
Nirav

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources