Result from rows to column - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Result from rows to column

Author

Topic

sardinka
Posting Yak Master

142 Posts

Posted - 2006-11-30 : 11:38:49

I am attachign script for my question as well as what I need to get as a result. Can you help me,please
CREATE TABLE [dbo].[Table1] (
[NameLast] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NameFirst] [varchar] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NameMiddle] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MemberID] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Table2] (
[MemberID] [int] NULL ,
[PlanType] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EffectiveDate] [smalldatetime] NULL ,
[TerminationDate] [smalldatetime] NULL
) ON [PRIMARY]
GO

INSERT INTO TABLE2
(MemberID,PlanType,EffectiveDate,TerminationDate)
VALUES
(222222,'010','01/01/2000','01/01/2001')

INSERT INTO TABLE2
(MemberID,PlanType,EffectiveDate,TerminationDate)
VALUES
(222222,'011','02/01/2001','03/01/2001')

INSERT INTO TABLE2
(MemberID,PlanType,EffectiveDate,TerminationDate)
VALUES
(333333,'010','01/01/2000','01/01/2001')

INSERT INTO TABLE2
(MemberID,PlanType,EffectiveDate,TerminationDate)
VALUES
(333333,'011','02/01/2001','03/01/2001')

INSERT INTO TABLE2
(MemberID,PlanType,EffectiveDate,TerminationDate)
VALUES
(333333,'05','02/01/2001','03/01/2001')

INSERT INTO TABLE2
(MemberID,PlanType,EffectiveDate,TerminationDate)
VALUES
(111111,'48','02/01/2001','03/01/2006')

INSERT INTO TABLE1
(NameLast,NameFirst,NameMiddle,MemberID)
VALUES
('NameLast2','NameFirst2','N',222222)
INSERT INTO TABLE1
(NameLast,NameFirst,NameMiddle,MemberID)
VALUES
('NameLast3','NameFirst1','N',333333)

INSERT INTO TABLE1
(NameLast,NameFirst,NameMiddle,MemberID)
VALUES
('NameLast1','NameFirst1','N',111111)
INSERT INTO TABLE1
(NameLast,NameFirst,NameMiddle,MemberID)
VALUES
('NameLast4','NameFirst4','N',44444)

--- I need to get thit result
MEMBERID PlanType EffectiveDate Terminationdate PlanType EffectiveDate Terminationdate etc..
222222 010 1/1/2000 1/1/2001 011 2/1/2001 3/1/2001
333333 010 1/1/2000 1/1/2001 011 2/1/2001 3/1/2001 05 2/1/2001 3/1/2001
111111 48 2/1/2001 3/1/2006

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-30 : 11:56:28

Why would you want to do that? I cannot think of any time that data would be useful. If you really wanted to print it out that way (and I don't think you would because paper has a limited width, but over multiple pages unlimited length) then you still need to query the data as rows and then do the single line formatting in your reporting/front-end application.

Cross tabs are very popular when you are aggregating data but in a case like this I'd strongly recommend keeping the data in the rows exactly like it is already, in Table2.

sardinka
Posting Yak Master

142 Posts

Posted - 2006-11-30 : 13:53:54

I need to see one row per memeber with all info, not multiply rows

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-30 : 14:23:46

I realise that, but what I'm telling you is that in pretty much every case that is going to be very difficult to work with. Do you know for sure how many plan types there are per member? - probably not, so then the results could be extremely wide and cause all sorts of problems.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-30 : 16:04:33

PIVOT or CROSSTAB report ring a bell for me...

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-01 : 03:19:44

quote:
Originally posted by Peso

PIVOT or CROSSTAB report ring a bell for me...

Peter Larsson
Helsingborg, Sweden

No doubt

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources