January 29, 2016 at 1:18 am
Hi Experts,
Please help on below.
How can i accomplish this query using t-sql.
below is the original data arrangement.
ID Name Value
1user MYS12345
2user MYS12346
3user MYS123435
4user1 MYS12312313
5user1 MYS1234567890
6user MYS1234567890
How can i achieve this style.
ID Name Value1 Value2 Value3 Value4
1 user MYS12345 MYS12346 MYS123435 MYS1234567890
2 user1 MYS12312313 MYS1234567890
Or do you have suggestion on how can i accomplish above.
Thanks.
January 29, 2016 at 12:36 pm
How will you determine which value goes into which column?
What will you do when there are more values for a column?
Really, this appears to be a formatting request, and formatting is handled much better at the client side. Try to avoid formatting in the database.
January 29, 2016 at 1:01 pm
Thank you for trying to post test data and your desired output. Next time it would be ideal if you could provide the test data in the form of a table definition (CREATE TABLE) plus a series of INSERT statements to populate the test table so we can easily setup an environment on our side to help you write the query.
I think your test data might be flawed. As Hugo pointed out, your desired results are not telling us the story so how would you know which value to put into each column?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 29, 2016 at 1:12 pm
This is an example on how you can do it using a crosstab.
DECLARE @myTable TABLE (ID INT, Name VARCHAR(10), Value VARCHAR(20))
INSERT INTO @myTable
VALUES
(1, 'user', 'MYS12345'),
(2, 'user', 'MYS12346'),
(3, 'user', 'MYS123435'),
(4, 'user1', 'MYS12312313'),
(5, 'user1', 'MYS1234567890'),
(6, 'user', 'MYS1234567890')
SELECT
Name,
MAX(CASE WHEN myRank = 1 THEN Value END) AS Value1,
MAX(CASE WHEN myRank = 2 THEN Value END) AS Value2,
MAX(CASE WHEN myRank = 3 THEN Value END) AS Value3,
MAX(CASE WHEN myRank = 4 THEN Value END) AS Value4
FROM
(
SELECT RANK() OVER (PARTITION BY Name ORDER BY Value) AS myRank, Name, Value FROM @myTable
) x
GROUP BY Name
HOWEVER, this is not a full answer to your question.
1. The solution implies that you have a maximum of 4 possible entries per person. An unknown number of columns would require a Dynamic SQL solution.
2. Also assumes the name is completely unique because your ID column in this case is completely worthless.
Rather it should be like this.
DECLARE @myTable TABLE (ID INT, Name VARCHAR(10), Value VARCHAR(20))
INSERT INTO @myTable
VALUES
(1, 'user', 'MYS12345'),
(1, 'user', 'MYS12346'),
(1, 'user', 'MYS123435'),
(2, 'user1', 'MYS12312313'),
(2, 'user1', 'MYS1234567890'),
(1, 'user', 'MYS1234567890')
SELECT
ID,
Name,
MAX(CASE WHEN myRank = 1 THEN Value END) AS Value1,
MAX(CASE WHEN myRank = 2 THEN Value END) AS Value2,
MAX(CASE WHEN myRank = 3 THEN Value END) AS Value3,
MAX(CASE WHEN myRank = 4 THEN Value END) AS Value4
FROM
(
SELECT RANK() OVER (PARTITION BY ID ORDER BY Value) AS myRank, ID, Name, Value FROM @myTable
) x
GROUP BY ID, Name
January 29, 2016 at 1:18 pm
yb751 (1/29/2016)
This is an example on how you can do it using a crosstab.
DECLARE @myTable TABLE (ID INT, Name VARCHAR(10), Value VARCHAR(20))INSERT INTO @myTable
VALUES
(1, 'user', 'MYS12345'),
(2, 'user', 'MYS12346'),
(3, 'user', 'MYS123435'),
(4, 'user1', 'MYS12312313'),
(5, 'user1', 'MYS1234567890'),
(6, 'user', 'MYS1234567890')
SELECT
Name,
MAX(CASE WHEN myRank = 1 THEN Value END) AS Value1,
MAX(CASE WHEN myRank = 2 THEN Value END) AS Value2,
MAX(CASE WHEN myRank = 3 THEN Value END) AS Value3,
MAX(CASE WHEN myRank = 4 THEN Value END) AS Value4
FROM
(
SELECT RANK() OVER (PARTITION BY Name ORDER BY Value) AS myRank, Name, Value FROM @myTable
) x
GROUP BY Name
HOWEVER, this is not a full answer to your question.
1. The solution implies that you have a maximum of 4 possible entries per person. Anything else would require a Dynamic SQL solution.
2. Also assumes the name is completely unique because your ID column in this case is completely worthless.
Rather it should be like this.
DECLARE @myTable TABLE (ID INT, Name VARCHAR(10), Value VARCHAR(20))INSERT INTO @myTable
VALUES
(1, 'user', 'MYS12345'),
(1, 'user', 'MYS12346'),
(1, 'user', 'MYS123435'),
(2, 'user1', 'MYS12312313'),
(2, 'user1', 'MYS1234567890'),
(1, 'user', 'MYS1234567890')
SELECT
ID,
Name,
MAX(CASE WHEN myRank = 1 THEN Value END) AS Value1,
MAX(CASE WHEN myRank = 2 THEN Value END) AS Value2,
MAX(CASE WHEN myRank = 3 THEN Value END) AS Value3,
MAX(CASE WHEN myRank = 4 THEN Value END) AS Value4
FROM
(
SELECT RANK() OVER (PARTITION BY ID ORDER BY Value) AS myRank, ID, Name, Value FROM @myTable
) x
GROUP BY ID, Name
nice....but when there is an unknown number of values, how does the dynamic sql work?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 29, 2016 at 1:25 pm
J Livingston SQL (1/29/2016)
nice....but when there is an unknown number of values, how does the dynamic sql work?
LOL...yeah now that you mention it, that would be a complete pain if possible.
I'd suppose you could do it by finding the highest value of a count when grouping the ID. Then using that to loop through the MAX statements in the crosstab perhaps??
January 29, 2016 at 1:51 pm
J Livingston SQL (1/29/2016)
nice....but when there is an unknown number of values, how does the dynamic sql work?
Here is a stab at it...but I'm not saying it's pretty. 😉
CREATE TABLE #myTABLE (ID INT, Name VARCHAR(10), Value VARCHAR(20))
DECLARE @most INT
DECLARE @stmt1 VARCHAR(MAX)
DECLARE @stmt2 VARCHAR(MAX)
INSERT INTO #myTABLE
VALUES
(1, 'user', 'MYS12345'),
(1, 'user', 'MYS12346'),
(1, 'user', 'MYS123435'),
(2, 'user1', 'MYS12312313'),
(2, 'user1', 'MYS1234567890'),
(1, 'user', 'MYS1234567890')
SET @most = (SELECT MAX(x.myCount) FROM (SELECT ID, COUNT(Name) AS myCount FROM #myTABLE GROUP BY ID) x )
DECLARE @i INT = 1
WHILE @i <= @most
BEGIN
SET @stmt1 = ISNULL(@stmt1,'') + 'MAX(CASE WHEN myRank = ' + CAST(@i AS CHAR(2)) + ' THEN Value END) AS Value' + CAST(@i AS CHAR(2)) + ','
SET @i = @i + 1
END
SET @stmt1 = SUBSTRING(@stmt1, 1, LEN(@stmt1)-1)
SET @stmt2 = '
SELECT
ID,
Name,
'
+ @stmt1 + '
FROM
(
SELECT RANK() OVER (PARTITION BY ID ORDER BY Value) AS myRank, ID, Name, Value FROM #myTABLE
) x
GROUP BY ID, Name'
EXECUTE (@stmt2)
DROP TABLE #myTABLE
January 29, 2016 at 1:58 pm
yb751 (1/29/2016)
J Livingston SQL (1/29/2016)
nice....but when there is an unknown number of values, how does the dynamic sql work?
LOL...yeah now that you mention it, that would be a complete pain if possible.
I'd suppose you could do it by finding the highest value of a count when grouping the ID. Then using that to loop through the MAX statements in the crosstab perhaps??
Loop? Pain? Not with the right tools. 🙂
Just comment as needed.
CREATE TABLE #myTable (ID INT, Name VARCHAR(10), Value VARCHAR(20))
INSERT INTO #myTable
VALUES
(1, 'user', 'MYS12345'),
(2, 'user', 'MYS12346'),
(3, 'user', 'MYS123435'),
(4, 'user1', 'MYS12312313'),
(5, 'user1', 'MYS1234567890'),
(6, 'user', 'MYS1234567890')
DECLARE @SQL nvarchar(max);
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT TOP(SELECT TOP 1 MAX(COUNT(DISTINCT Value)) OVER()
FROM #myTable
GROUP BY Name)
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E4
)
SELECT @SQL = N'SELECT ROW_NUMBER() OVER(ORDER BY Name) AS ID ' + NCHAR(10)
+ N' ,Name AS Name ' + NCHAR(10)
+ ( SELECT ' ,MAX(CASE WHEN myRank = ' + CAST( n AS varchar(3)) + ' THEN Value END) AS Value' + CAST( n AS varchar(3)) + NCHAR(10)
FROM cteTally
FOR XML PATH(''),TYPE).value('./text()[1]', 'nvarchar(MAX)')
+ 'FROM (SELECT RANK() OVER (PARTITION BY Name ORDER BY ID) AS myRank, ID, Name, Value FROM #myTable) x ' + NCHAR(10)
+ 'GROUP BY Name;';
PRINT @SQL;
EXECUTE sp_executesql @SQL;
GO
DROP TABLE #myTable
January 29, 2016 at 2:06 pm
Luis Cazares (1/29/2016)
yb751 (1/29/2016)
J Livingston SQL (1/29/2016)
nice....but when there is an unknown number of values, how does the dynamic sql work?
LOL...yeah now that you mention it, that would be a complete pain if possible.
I'd suppose you could do it by finding the highest value of a count when grouping the ID. Then using that to loop through the MAX statements in the crosstab perhaps??
Loop? Pain? Not with the right tools. 🙂
Just comment as needed.
CREATE TABLE #myTable (ID INT, Name VARCHAR(10), Value VARCHAR(20))
INSERT INTO #myTable
VALUES
(1, 'user', 'MYS12345'),
(2, 'user', 'MYS12346'),
(3, 'user', 'MYS123435'),
(4, 'user1', 'MYS12312313'),
(5, 'user1', 'MYS1234567890'),
(6, 'user', 'MYS1234567890')
DECLARE @SQL nvarchar(max);
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT TOP(SELECT TOP 1 MAX(COUNT(DISTINCT Value)) OVER()
FROM #myTable
GROUP BY Name)
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E4
)
SELECT @SQL = N'SELECT ROW_NUMBER() OVER(ORDER BY Name) AS ID ' + NCHAR(10)
+ N' ,Name AS Name ' + NCHAR(10)
+ ( SELECT ' ,MAX(CASE WHEN myRank = ' + CAST( n AS varchar(3)) + ' THEN Value END) AS Value' + CAST( n AS varchar(3)) + NCHAR(10)
FROM cteTally
FOR XML PATH(''),TYPE).value('./text()[1]', 'nvarchar(MAX)')
+ 'FROM (SELECT RANK() OVER (PARTITION BY Name ORDER BY ID) AS myRank, ID, Name, Value FROM #myTable) x ' + NCHAR(10)
+ 'GROUP BY Name;';
PRINT @SQL;
EXECUTE sp_executesql @SQL;
GO
DROP TABLE #myTable
Nice Luis...my 'mad' skills are quite up to par with you yet. LOL I haven't played around with tally tables yet but it's on my list. I'm at least content I was able to put my money where my mouth is and come up with a viable solution. Also notice how I used crosstabs instead of a pivot. 😎 I knew if I started preaching pivots again I'd never hear the end of it. LOL
January 29, 2016 at 5:12 pm
Luis Cazares (1/29/2016)
yb751 (1/29/2016)
J Livingston SQL (1/29/2016)
nice....but when there is an unknown number of values, how does the dynamic sql work?
LOL...yeah now that you mention it, that would be a complete pain if possible.
I'd suppose you could do it by finding the highest value of a count when grouping the ID. Then using that to loop through the MAX statements in the crosstab perhaps??
Loop? Pain? Not with the right tools. 🙂
Just comment as needed.
CREATE TABLE #myTable (ID INT, Name VARCHAR(10), Value VARCHAR(20))
INSERT INTO #myTable
VALUES
(1, 'user', 'MYS12345'),
(2, 'user', 'MYS12346'),
(3, 'user', 'MYS123435'),
(4, 'user1', 'MYS12312313'),
(5, 'user1', 'MYS1234567890'),
(6, 'user', 'MYS1234567890')
DECLARE @SQL nvarchar(max);
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT TOP(SELECT TOP 1 MAX(COUNT(DISTINCT Value)) OVER()
FROM #myTable
GROUP BY Name)
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E4
)
SELECT @SQL = N'SELECT ROW_NUMBER() OVER(ORDER BY Name) AS ID ' + NCHAR(10)
+ N' ,Name AS Name ' + NCHAR(10)
+ ( SELECT ' ,MAX(CASE WHEN myRank = ' + CAST( n AS varchar(3)) + ' THEN Value END) AS Value' + CAST( n AS varchar(3)) + NCHAR(10)
FROM cteTally
FOR XML PATH(''),TYPE).value('./text()[1]', 'nvarchar(MAX)')
+ 'FROM (SELECT RANK() OVER (PARTITION BY Name ORDER BY ID) AS myRank, ID, Name, Value FROM #myTable) x ' + NCHAR(10)
+ 'GROUP BY Name;';
PRINT @SQL;
EXECUTE sp_executesql @SQL;
GO
DROP TABLE #myTable
Very nice, Luis. Good creativity on making the dynamic crosstab.
January 29, 2016 at 5:16 pm
yb751 (1/29/2016)
Nice Luis...my 'mad' skills are quite up to par with you yet. LOL I haven't played around with tally tables yet but it's on my list. I'm at least content I was able to put my money where my mouth is and come up with a viable solution. Also notice how I used crosstabs instead of a pivot. 😎 I knew if I started preaching pivots again I'd never hear the end of it. LOL
Well, that item on your list will change the way you look at data. I can also change your expectations of performance. If you aren't familiar with it yet, then prepare to have your mind twisted a bit into a new paradigm of thought.
Start here: http://www.sqlservercentral.com/articles/T-SQL/62867/
Continue here: http://www.sqlservercentral.com/articles/Tally+Table/72993/
Do some playing, then start looking at your own procedures differently. Enjoy the trip...it's a fun one. 😉
February 1, 2016 at 3:04 am
Thanks your you help it works like a charm.
And next time i will provide a test data in the form of a table definition.
Thanks again.
May 13, 2016 at 5:46 pm
please research if you can use Pivot table in t-sql, does not limit how many columns in the string or data
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply