Need help with the query. - 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 2008 Forums

Transact-SQL (2008)

Need help with the query.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2011-01-11 : 13:54:00

[code]Hi all,

Given the table and data below. How can I get the desired output below. Basislly, convert columns data into rows.

Thanks in advance.

DROP TABLE [dbo].[StagingCust]
GO
CREATE TABLE [dbo].[StagingCust](
[CustomerID] [varchar](5) NULL,
[ContactLastName1] [varchar](10) NULL,
[ContactFirstName1] [varchar](10) NULL,
[ContactLastName2] [varchar](10) NULL,
[ContactFirstName2] [varchar](10) NULL,
[ContactLastName3] [varchar](10) NULL,
[ContactFirstName3] [varchar](10) NULL
) ON [PRIMARY]

GO

SET NOCOUNT ON;
SET XACT_ABORT ON;
GO

BEGIN TRANSACTION;
INSERT INTO [dbo].[StagingCust]([CustomerID], [ContactLastName1], [ContactFirstName1], [ContactLastName2], [ContactFirstName2], [ContactLastName3], [ContactFirstName3])
SELECT N'BOA', N'Smith ', N'Lisa', N'Lee', N'Peter', N'Nguyen', N'Peter' UNION ALL
SELECT N'KON', N'Lorenze', N'Marine', N'Melissa', N'Bloch', 'Doe', N'User3'
COMMIT;
RAISERROR (N'[dbo].[StagingCust]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO

--Testing..
SELECT CustomerID,
CASE LName
WHEN 1 THEN ContactLastName1
WHEN 2 THEN ContactLastName2
WHEN 3 THEN ISNULL(ContactLastName3, '')
END AS 'LastName'
FROM StagingCust
CROSS JOIN (SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3) AS a(LName)

go

SELECT *
FROM StagingCust;
go

-- Desired output:
CustomerID LastName FirstName
---------- ---------- ---------
BOA Smith Lisa
BOA Lee Peter
BOA Nguyen Peter

KON Lorenze Marine
KON Melissa Bloch
KON Doe User3[/code]

mikgri
Starting Member

39 Posts

Posted - 2011-01-13 : 09:47:26

Try this:
SELECT CustomerID=case LNAME when 4 then '' else CustomerID end,
CASE LName
WHEN 1 THEN ContactLastName1
WHEN 2 THEN ContactLastName2
WHEN 3 THEN ISNULL(ContactLastName3, '')
when 4 then ''
END AS 'LastName',
CASE LName
WHEN 1 THEN ContactFirstName1
WHEN 2 THEN ContactFirstName2
WHEN 3 THEN ISNULL(ContactFirstName3, '')
when 4 then ''
END AS 'FirstName'
FROM StagingCust
CROSS JOIN (SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
select 4) AS a(LName)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-13 : 10:53:23

didnt i give you solution here?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=155157

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources