Solved

SQL Query Help

Posted on 2010-11-10
4
289 Views
Last Modified: 2012-05-10
SQL Server 2005 table:

ID   NewID
01    A01
02    A02
03   A031
03   A032
03   A033
03   A034
04   A041
04  A04 etc.  (NewId is unique)

Desired output:
ID  NewID1 NewID2 NewID3 NewID4 NewID5
01    A01
02    A02
03   A031    A032    A033     A034
04   A041      A04

Do I have to set in advance how many NewID columns are in the output?
Can it be dynamic?

Thank you very much!

0
Comment
Question by:larisa1970
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 8

Expert Comment

by:kingjely
ID: 34108924


Hi Larisa
In mysql this would do what you need.. I'm not sure if it will work in mssql but worth a go

SELECT ID, GROUP_CONCAT(NEWID)
FROM YOURTABLE
GROUP BY ID
0
 
LVL 2

Expert Comment

by:marat-oz
ID: 34110131
try this, but number of possible NEWIDs is hardcoded

WITH T (ID, [NEWID], GID)
AS
(
select *, row_number() over (partition by ID order by ID, [NewID])
from <YourTable>
)
select t1.ID, COALESCE(NEWID1,'') NEWID1, COALESCE(NEWID2,'') NEWID2, COALESCE(NEWID3,'') NEWID3, COALESCE(NEWID4,'') NEWID4, COALESCE(NEWID5,'') NEWID5
from  
(select ID, NEWID1 = [NewID] from T where t.GID = 1) t1
LEFT JOIN
(select ID, NEWID2 = [NewID] from T where t.GID = 2) t2 on t1.ID = t2.ID
LEFT JOIN
(select ID, NEWID3 = [NewID] from T where t.GID = 3) t3 on t1.ID = t3.ID
LEFT JOIN
(select ID, NEWID4 = [NewID] from T where t.GID = 4) t4 on t1.ID = t4.ID
LEFT JOIN
(select ID, NEWID5 = [NewID] from T where t.GID = 5) t5 on t1.ID = t5.ID

0
 
LVL 6

Expert Comment

by:dan_mason
ID: 34110952
This will give you the number of columns needed, and create them...
DECLARE @numCol int, @counter int=1, @sql varchar(200)
CREATE TABLE #output (ID char(2))
DECLARE @input table (ID char(2),NewID varchar(5))
INSERT @input (ID,NewID)
VALUES
('01','A01'),('02','A02'),('03','A031'),('03','A032'),('03','A033'),('03','A034'),('04','A041')

INSERT #output (ID)
SELECT DISTINCT ID FROM @input

SELECT @numCol= MAX(n) FROM(SELECT(COUNT(1)) as n FROM @input GROUP BY ID) a

WHILE @counter<=@numCol
	BEGIN
		SET @sql='ALTER TABLE #output ADD NewID'+CAST(@counter as varchar)+' varchar(5)'
		EXEC (@sql)
		SET @counter=@counter+1
	END

SELECT * FROM #output
DROP TABLE #output

Open in new window

0
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
ID: 34112243
You don't have to ALTER your table to do that.
I assume you don't know how many NewID you will have there per ID correct so basically you don't know how many columns you will need. Hence, you have to go with dynamic SQL. Check the below:

declare @strSQL varchar(max)
declare @cols varchar(max)

set @cols = stuff((select distinct 
			'], [' + cast(row_number() over (partition by id order by [NewID]) as varchar)
		from yourtable
		for xml path('')), 1, 2, '') + ']'

set @strSQL = 	' select id, ' + @cols +
		' from (select	id, 
				[NewID],
				row_number() over (partition by ID order by NewID) rn
			from yourtable
		 ) o
		pivot (max([NewID]) for rn in (' + @cols + ')) p'

exec(@strSQL)

Open in new window

0

Featured Post

Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question