multiple column in one column with same id
-
create table mytable (id int identity(1,1), PersonID int, Unit varchar(10))
insert into mytable values (1,'Che')
insert into mytable values (1,'Mat')
insert into mytable values (1,'Phy')
insert into mytable values (2,'Che2')
insert into mytable values (2,'Mat2')
insert into mytable values (2,'Phy2')
insert into mytable values (3,'Phy3')
I want multiple column in one column with same id...something like
Person ID Unit
1 Che
Mat
Phy
--------------------------------------------
2 Che2
Mat2
Phy2
--------------------------------------------
3 Phy3
-
This is about as close as you can get, taking the question literally:
SELECTCASE
WHEN Numbered.RowNumber = 1
THEN CONVERT(varchar(12), Numbered.PersonID)
ELSE ''
END AS PersonID,
Numbered.Unit
FROM
(
SELECT
RowNumber =
ROW_NUMBER() OVER (
PARTITION BY m.PersonID
ORDER BY m.id),
m.*
FROM dbo.mytable AS m
) AS Numbered
ORDER BY
Numbered.PersonID,
Numbered.id;
Output:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
Thanks for your reply, however i think i did not explain my self correctly.
I need multiple row in one column..
so instead of having seperate rows for UNITS column I just need one column for id 1 which has all the units seperated by a new line and like wise for 2 and 3..
-
create table mytable (id int identity(1,1), PersonID int, Unit varchar(10))
insert into mytable values (1,'Che')
insert into mytable values (1,'Mat')
insert into mytable values (1,'Phy')
insert into mytable values (2,'Che2')
insert into mytable values (2,'Mat2')
insert into mytable values (2,'Phy2')
insert into mytable values (3,'Phy3')
SELECT t1.PersonID,
Units =REPLACE( (SELECT Unit AS [data()]
FROM mytable t2
WHERE t2.PersonID = t1.PersonID
ORDER BY Unit
FOR XML PATH('')
), ' ', ',')
FROM mytable t1
GROUP BY PersonID ;
drop table mytable
If you check the result here...the units column data is sepreated by a 'comma'.
PersonIDUnits1Che,Mat,Phy
2Che2,Mat2,Phy2
3Phy3
My result should be like
PersonIDUnits1Che
Mat
Phy
2Che2
Mat2
Phy2
3Phy3
-
If you want it to be part of the same records, you can replace the commas with char(10) + char(13). If you want it to be different records, you can use Paul's way
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
i can't do it like that..
-
mario.balatellii (1/16/2012)
i can't do it like that..This gets posted at least once a week. . .
BEGIN TRANSACTIONCREATE TABLE mytable (id INT identity(1, 1),PersonID INT,Unit VARCHAR(10))
INSERT INTO mytable VALUES (1,'Che')
INSERT INTO mytable VALUES (1,'Mat')
INSERT INTO mytable VALUES (1,'Phy')
INSERT INTO mytable VALUES (2,'Che2')
INSERT INTO mytable VALUES (2,'Mat2')
INSERT INTO mytable VALUES (2,'Phy2')
INSERT INTO mytable VALUES (3,'Phy3')
SELECT PersonID, STUFF((SELECT ',' + Unit
FROM mytable t2
WHERE t2.PersonID = t1.PersonID
FOR XML PATH('')), 1, 1, '') AS Units
FROM mytable t1
GROUP BY PersonID
ROLLBACK
--EDIT--
Or another way.
SELECT PersonID, (SELECT grpUnits.grpUnitFROM (SELECT ',' + Unit
FROM mytable t2
WHERE t2.PersonID = t1.PersonID
FOR XML PATH(''), TYPE) units(unit)
CROSS APPLY (SELECT STUFF(units.unit.value('./text()[1]', 'VARCHAR(MAX)'),1,1,'')) grpUnits(grpUnit)) AS Unit
FROM mytable t1
GROUP BY PersonID
Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
mario.balatellii (1/16/2012)
Thanks for your reply, however i think i did not explain my self correctly.I need multiple row in one column..
so instead of having seperate rows for UNITS column I just need one column for id 1 which has all the units seperated by a new line and like wise for 2 and 3..
Can you tell us more about why you need it this way? It sounds like you are producing a report or something like that. Who is it for, and how will they use it? I can't imagine you want the results to look the way you describe in SQL Server Management Studio! This sort of output formatting usually belongs in an application, not at the database layer. Store the data in SQL Server and present it to users through an application or web page e.g. Access, Excel, SSRS, and do the formatting there.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
thanks i used the comma seperation and worked out the code in c# to get the desired result :).
-
Apologies, I didn't read the whole thread when I replied.
BEGIN TRANSACTIONCREATE TABLE mytable (id INT identity(1, 1),PersonID INT,Unit VARCHAR(10))
INSERT INTO mytable VALUES (1,'Che')
INSERT INTO mytable VALUES (1,'Mat')
INSERT INTO mytable VALUES (1,'Phy')
INSERT INTO mytable VALUES (2,'Che2')
INSERT INTO mytable VALUES (2,'Mat2')
INSERT INTO mytable VALUES (2,'Phy2')
INSERT INTO mytable VALUES (3,'Phy3')
--Option 1
SELECT PersonID, REPLACE(STUFF((SELECT '|,|' + Unit
FROM mytable t2
WHERE t2.PersonID = t1.PersonID
FOR XML PATH('')), 1, 3, ''),'|,|',CHAR(10) + CHAR(13)) AS Units
FROM mytable t1
GROUP BY PersonID
--Option 2
SELECT PersonID, REPLACE((SELECT grpUnits.grpUnit
FROM (SELECT '|,|' + Unit
FROM mytable t2
WHERE t2.PersonID = t1.PersonID
FOR XML PATH(''), TYPE) units(unit)
CROSS APPLY (SELECT STUFF(units.unit.value('./text()[1]', 'VARCHAR(MAX)'),1,3,'')
) grpUnits(grpUnit)),'|,|',CHAR(10) + CHAR(13)) AS Unit
FROM mytable t1
GROUP BY PersonID
ROLLBACK
Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
mario.balatellii (1/16/2012)
thanks i used the comma seperation and worked out the code in c# to get the desired result :).I do like a happy ending 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
What is correct term for this SQL problem. Would it be called single value Pivot?
-
SQL DBA 808 (1/16/2012)
What is correct term for this SQL problem. Would it be called single value Pivot?If you mean the idea of presenting multiple columns in one row, I have no idea - I'd never come across this requirement until today. Single column pivot maybe? Not sure it will catch on 😀
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
SQL DBA 808 (1/16/2012)
What is correct term for this SQL problem. Would it be called single value Pivot?Well, I call it a "Grouped Delimited List"
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
mario.balatellii (1/16/2012)
i can't do it like that..Gosh... if you set the output mode of the result window to TEXT with "columns" instead of the GRID mode, then Paul's answer would work perfectly.
It would also be very interesting to see what your C# code ended up looking like. Would you mind posting it?
--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)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply