How to convert vertical records to horizontal?

Question

Post reply

How to convert vertical records to horizontal?

Raymond-122342

SSC-Addicted

Points: 457
More actions
February 13, 2004 at 11:26 pm

#107822

Could some please help me with how to write this query in T-SQL? Say there is a product table with just 1 column, ProductID, with the following rows:

1

2

3

4

how to generate a string with one query (no cursor) to concat ProductID? i.e., in this case, to generate string '1234' with one query.

Please help?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 1

only if you have some unique identifier.

select a.productid + b.productid + c.productid

from Product a, product b, product c

where a.productid = 'A' and b.productid = 'B' and c.productid = 'C'

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 2

I think Raymond needs the concat value rather than the sum

SELECT CAST(a.ID AS varchar(1)) + CAST(b.ID AS varchar(1)) + CAST(c.ID AS varchar(1)) + CAST(d.ID AS varchar(1)) AS Expr1

FROM MAINCATEGORY a CROSS JOIN

MAINCATEGORY b CROSS JOIN

MAINCATEGORY c CROSS JOIN

MAINCATEGORY d

WHERE (a.ID = 1) AND (b.ID = 2) AND (c.ID = 3) AND (d.ID = 4)

My Blog: http://dineshasanka.spaces.live.com/

Raymond-122342 SSC-Addicted Points: 457 More actions · Answer 3

Denish/Steve,

Thanks for the reply. That was actually an interview question that I was asked last week, and I came with something like:

select CAST(a.ID AS varchar(1)) + CAST(b.ID AS varchar(1)) + CAST(c.ID AS varchar(1)) + CAST(d.ID AS varchar(1))

FROM

(select ID from product where id=1) a,

(select ID from product where id=2) b,

select ID from product where id=3) c,

select ID from product where id=4) d

As you can see, all our solutions seem to work, but we all rely on hard-code 1/2/3/4. So if the table has records from 1-100, ours will look very awkward.

The interviewer mentioned to me the easiest solution something like:

declard @x varchar(100)

select @x=@x+CAST(ID as varchar(1)) FROM Product

Print @X

The idea was that SELECT will loop through all rows, but I could not get this working.

Thoughts?

bp SSCarpal Tunnel Points: 4656 More actions · Answer 4

Raymond, you are on right track but you must remember that NULL concatentated with something is NULL.

When you first assign value to @x, @x is NULL.

Therefore you need to set @x = '' from the very beginning or use ISNULL.

Also be careful with cast(1) as it only has enough space for numbers from 0 to 9.

Using Northwind as an example...

--- cut here ---

USE NORTHWIND

GO

declare @x varchar(100)

set @x = ''

select @x = @x+ '/' +CAST(productid as varchar(2)) FROM Products

WHERE PRODUCTID < 100

Print @X

--- cut here ---

HTH

Billy

Richard Lesh Right there with Babe Points: 761 More actions · Answer 5

Richard Lesh

Right there with Babe

Points: 761

February 15, 2004 at 2:35 pm

#494111

If no scripting allowed, how about....

select min(a.ProductID), min(b.ProductID), min(c.ProductID), min(d.ProductID)  
  from Table1 a
    left join Table1 b on b.ProductID > a.ProductID
    left join Table1 c on c.ProductID > b.ProductID
    left join Table1 d on d.ProductID > c.ProductID

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Frank Kalis

SSC Guru

Points: 111183

February 15, 2004 at 2:37 pm

#494112

Well, interview question or not. My answer would be

Do this at the client! The server is for data retrieval, not presentational stuff.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

Frank,

Don't be so quick to give up on the data layer. I had a similar challenge... I've posted ALL the code including the example data and table creation. The actual code that does the work you are looking for is very short. If you don't need the delimiter, set the delimiter to '' and you'll be OK.

--Jeff Moden

Here's the code...

SET NOCOUNT ON

GO

/*===== SETUP DEMONSTRATION FOR IMPROVED MAKEMODEL PROBLEM =====*/

----- DROP MAKEMODEL TABLE IF IT EXISTS

IF EXISTS

(SELECT * FROM SYSOBJECTS WHERE [NAME] = 'MakeModel')

DROP TABLE dbo.MakeModel

GO

----- IF THE NEW FUNCTION EXISTS, DROP IT -----

IF EXISTS

(SELECT * FROM SYSOBJECTS WHERE [NAME] = 'fn_DelimitRow')

DROP FUNCTION dbo.fn_DelimitRow

GO

----- CREATE THE MAKEMODEL TABLE -----

CREATE TABLE dbo.MakeModel

(

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

[MAKE] [varchar] (10) NULL ,

[MODEL] [varchar] (20) NULL

&nbsp ON [PRIMARY]

GO

----- POPULATE THE MAKE MODEL TABLE WITH EXAMPLE DATA -----

INSERT INTO MakeModel (MAKE,MODEL)

VALUES ('FORD','MUSTANG')

INSERT INTO MakeModel (MAKE,MODEL)

VALUES ('CHEVY','MALIBU')

INSERT INTO MakeModel (MAKE,MODEL)

VALUES ('CHEVY','NOVA')

INSERT INTO MakeModel (MAKE,MODEL)

VALUES ('LINCOLN','CONTINENTAL')

INSERT INTO MakeModel (MAKE,MODEL)

VALUES ('FORD','NAVIGATOR')

INSERT INTO MakeModel (MAKE,MODEL)

VALUES ('FORD','F150 TRUCK')

INSERT INTO MakeModel (MAKE,MODEL)

VALUES ('GMC','SIERRA')

INSERT INTO MakeModel (MAKE,MODEL)

VALUES ('CHEVY','S10')

GO

----- DISPLAY CONTENTS OF TABLE TO VERIFY

SELECT *

FROM MakeModel

GO

/*===== CREATE THE NEW FUNCTION =====*/

----- Could parameterize more for table and column names

-- and use with dynamic SQL to run

CREATE FUNCTION fn_DelimitRow (@Make VARCHAR(50),@Delim VARCHAR(10))

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @info VARCHAR(8000)

-- Next line is "JBM PFM!"

SELECT @info = COALESCE(@Info + @Delim, '') + RTRIM(MODEL)

FROM MakeModel

WHERE MAKE=@Make

ORDER BY MODEL

RETURN (@Info)

END

GO

/*===== NOW, DEMO NEW MAKEMODEL PROBLEM SOLUTION =====*/

----- No temp or working tables and no cursors!

----- Can pass any 1-10 character delimiter in single quotes

SELECT DISTINCT MAKE, dbo.fn_DelimitRow(MAKE,', ') AS MODELS

FROM MakeModel

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

kay SSCommitted Points: 1517 More actions · Answer 8

kay

SSCommitted

Points: 1517

February 16, 2004 at 7:15 am

#494169

if ProductID is a char, then try this:

declare @string_var varchar(255)

select @string_var = ""

select @string_var = @string_var + ProductID from tbl_a

print @string_var

When I first saw this, I could't believe it works. But it does. It must do its own recursion during execution....

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 9

Be careful... if any NULLS, answer will be NULL unless "Concatenate Nulls Yields Nulls" is turned off.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Sugar Bear Newbie Points: 5 More actions · Answer 10

Say I want the horizontal result to be individual columns instead of a concatenated string? For example:

CREATE TABLE

dbo.MetaData

(

[ID] [int]

IDENTITY (1, 1) NOT NULL ,

[FieldID] [int]

NULL ,

[FieldName] [varchar] (50)

NULL ,

[FieldValue] [varchar] (50)

NULL )

GO

----- POPULATE THE META DATA TABLE WITH EXAMPLE DATA -----

INSERT INTO