August 10, 2012 at 8:06 am
I have a table called 'warehouse_locations'. A row in the table looks like this:
site_idlocation_iddescription
0618a4 18a4
06 18a5 18a5
I need the results to be as follows:
location1 location2
18a4 18a5
I have looked at the pivot explanations and have not been successful so far. Please help
CRC
August 10, 2012 at 8:10 am
Ok, saw you edited. justa sec.
August 10, 2012 at 8:11 am
Sorry I accidentaly posted before I was finished writing.
CRC
August 10, 2012 at 8:14 am
CRC (8/10/2012)
I have a table called 'warehouse_locations'. A row in the table looks like this:site_idlocation_iddescription
0618a4 18a4
06 18a5 18a5
I need the results to be as follows:
location1 location2
18a4 18a5
I have looked at the pivot explanations and have not been successful so far. Please help
Ok, PIVOT turns row VALUES into COLUMN headings.
so, how do i know that location1 is 18a4 and location2 is 18a5?
is the number of columns you expect a fixed or dynamic number?
August 10, 2012 at 8:20 am
The number of columns returning are dynamic. I will be selecting a range and will need them returned as one row.
CRC
August 10, 2012 at 9:53 am
I can be wrong but I think dynamic SQL with the loop is the only way:
CREATE TABLE #Site (
site_id CHAR(2)
,location_id CHAR(4)
,[description] VARCHAR(100)
);
INSERT INTO #Site (site_id, location_id, [description])
VALUES ('06', '18a4', '18a4'),
('06', '18a5', '18a5'),
('06', '18a6', '18a6'),
('06', '18a7', '18a7'),
('06', '18a8', '18a8'),
('06', '18a8', '18a8'),
('06', '18a9', '18a9')
--SELECT * FROM #Site;
DECLARE @sql VARCHAR(1000);
DECLARE @i INT; SET @i = 0;
DECLARE @location_id CHAR(4);
CREATE TABLE #Temp (location_count INT);
INSERT INTO #Temp(location_count) VALUES(0);
WHILE EXISTS(SELECT * FROM #Site) BEGIN
SET @i = @i + 1;
SELECT TOP (1) @location_id = location_id FROM #Site;
SET @sql = 'ALTER TABLE #Temp ADD location' + CAST(@i AS VARCHAR(9)) + ' CHAR(4)';
EXEC (@sql);
SET @sql = 'UPDATE #Temp SET location' + CAST(@i AS VARCHAR(9)) + ' = ''' + @location_id + '''';
EXEC (@sql);
DELETE FROM #Site WHERE location_id = @location_id;
END;
UPDATE #Temp SET location_count = @i;
--Or you can delete location_count
--ALTER TABLE #Temp DROP COLUMN location_count;
SELECT * FROM #Temp;
DROP TABLE #Temp;
DROP TABLE #Site;
--Vadim R.
August 10, 2012 at 10:10 am
Thanks!!! That works great!
CRC
August 10, 2012 at 10:14 am
Glad it worked for you.
--Thanks.
--Vadim R.
August 10, 2012 at 10:34 am
whats supposed to happen if you have these records?
INSERT INTO #Site (site_id, location_id, [description])
VALUES ('06', '18a4', '18a4'),
('06', '18a5', '18a5'),
('06', '18a6', '18a6'),
('06', '18a7', '18a7'),
('06', '18a8', '18a8'),
('06', '18a9', '18a9'),
('07', '19a5', '19a5')
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 10, 2012 at 1:40 pm
In this particular instance there won't be different site id's because the data that will be inserted into the temp table will only be for one site.
CRC
August 10, 2012 at 8:27 pm
rVadim (8/10/2012)
I can be wrong but I think dynamic SQL with the loop is the only way:
CREATE TABLE #Site (
site_id CHAR(2)
,location_id CHAR(4)
,[description] VARCHAR(100)
);
INSERT INTO #Site (site_id, location_id, [description])
VALUES ('06', '18a4', '18a4'),
('06', '18a5', '18a5'),
('06', '18a6', '18a6'),
('06', '18a7', '18a7'),
('06', '18a8', '18a8'),
('06', '18a8', '18a8'),
('06', '18a9', '18a9')
--SELECT * FROM #Site;
DECLARE @sql VARCHAR(1000);
DECLARE @i INT; SET @i = 0;
DECLARE @location_id CHAR(4);
CREATE TABLE #Temp (location_count INT);
INSERT INTO #Temp(location_count) VALUES(0);
WHILE EXISTS(SELECT * FROM #Site) BEGIN
SET @i = @i + 1;
SELECT TOP (1) @location_id = location_id FROM #Site;
SET @sql = 'ALTER TABLE #Temp ADD location' + CAST(@i AS VARCHAR(9)) + ' CHAR(4)';
EXEC (@sql);
SET @sql = 'UPDATE #Temp SET location' + CAST(@i AS VARCHAR(9)) + ' = ''' + @location_id + '''';
EXEC (@sql);
DELETE FROM #Site WHERE location_id = @location_id;
END;
UPDATE #Temp SET location_count = @i;
--Or you can delete location_count
--ALTER TABLE #Temp DROP COLUMN location_count;
SELECT * FROM #Temp;
DROP TABLE #Temp;
DROP TABLE #Site;
The loop and the resulting multiple updates can be avoided. Please see the following article.
http://qa.sqlservercentral.com/articles/Crosstab/65048/
--Jeff Moden
August 10, 2012 at 11:14 pm
Jeff Moden (8/10/2012)
The loop and the resulting multiple updates can be avoided. Please see the following article.
Thank you, Jeff, great article.
OK, there is another way, without loop and ALTER/UPDATE.
CREATE TABLE #Site (
site_id CHAR(2)
,location_id CHAR(4)
,[description] VARCHAR(100)
);
INSERT INTO #Site (site_id, location_id, [description])
VALUES ('06', '18a4', '18a4'),
('06', '18a5', '18a5'),
('06', '18a6', '18a6'),
('06', '18a7', '18a7'),
('06', '18a9', '18a8'),
('06', '18a8', '18a8'),
('06', '18a9', '18a9')
--SELECT * FROM #Site
DECLARE @SQL VARCHAR(2000) = 'SELECT ';
DECLARE @i INT = 1;
WITH DistinctLocationsCTE AS (
SELECT DISTINCT location_id
FROM #Site
)
SELECT
@SQL += '''' + COALESCE(location_id, '') + ''' AS location' + CAST(@i AS VARCHAR(9)) + ','
,@i += 1
FROM DistinctLocationsCTE
ORDER BY location_id;
--Remove comma at the end
SET @SQL = LEFT(@SQL, LEN(@SQL)-1);
--PRINT @SQL
EXEC (@SQL);
DROP TABLE #Site;
--Vadim R.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply